Dave's Technophorical Times

A blog about Microsoft's Technologies!
SharePoint :: MVC :: ASP.NET :: IIS :: SQL Server :: Visual Studio :: MS Access

From here: https://mstechtalk.com/fix-recovery-pending-state-sql-server/

Solution 1 worked like a chalm!

States of SQL Server Databases

If a single or multiple core files cannot be accessed in SQL server, it means that the SQL server database is corrupted. As per the measure of damage in the database, there are different states of SQL database. Some of them are explained below:

Online: If a single file is damaged and cannot be accessed, the database will remain available and online.

Suspect: In case, the transaction log file is damaged and it is creating obstructions on recovery or preventing transaction rollback from completion, it will result in failure of SQL database.

Recovery Pending: This state usually occurs when the SQL server knows that recovery of the database is to be done, but something is creating hindrance before starting it. This state is different from the suspect state as it cannot be declared that database recovery will fail, but it has not started yet.

The state of a database can be known after executing the following query statement:

SELECT name, state_desc from sys.databases
GO

Reasons Behind Recovery Pending State in SQL Server

Once a reason is known, finding a solution becomes a lot easier. When SQL database requires recovery and it cannot be started, it is known as pending state. This situation arises when:

  • The database is not cleanly shut down. In other words, one or more unfulfilled transaction is active at that time and the transaction log file has been deleted.
  • Users attempted to transfer the transaction log files to a new drive to solve performance issues. But, this may leads to corruption of log files.
  • Due to inadequate memory space or disk storage, database recovery cannot be started.

Workaround to Fix Recovery Pending State in SQL Server

There are 2 different manual methods to initiate SQL database recovery, which is marked in the recovery pending state. Both the solutions are described below.

Solution 1: Mark SQL Database in Emergency Mode and Start Forceful Repair

Follow these steps to resolve SQL server database issue:

  1. Execute these queries to fix SQL server database in recovery pending state:
  2. ALTER DATABASE [DBName] SET EMERGENCY;
    GO
    ALTER DATABASE [DBName] set single_user
    GO
    DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
    GO
    ALTER DATABASE [DBName] set multi_user
    GO

  3. EMERGENCY mode marks the SQL database as READ_ONLY, deactivates logging, and gives the permission to system admin only
  4. This method is capable of resolving any technical issue and bringing the database back to the accessible state. The database will automatically come out of the EMERGENCY mode.

Solution 2: Mark SQL Database in Emergency Mode, Disconnect the Main Database and Attach it Again

Execute these commands to fix recovery pending state in SQL server:

ALTER DATABASE [DBName] SET EMERGENCY;
ALTER DATABASE [DBName] set multi_user
EXEC sp_detach_db ‘[DBName]’
EXEC sp_attach_single_file_db @DBName = ‘[DBName]’, @physname = N'[mdf path]’

These query statements will help the server to get corruption-free log and create a new one automatically.

Warning: Before performing any manual solution on the SQL server database, make sure that the proper backups of the data are created. In case, any error occurs, the data should still be available. Also, the manual approach is to be performed only when a user has a deep technical knowledge on the topic. If a user is unsure and the steps are not clear, another approach is explained in the next section.

Alternative Solution to Fix Recovery Pending State in SQL Server Database

No doubt, the manual solution to repair SQL server database is an effective technique, but it has to be performed with utmost care. Additionally, there are other disadvantages of the method too. So, it is recommended to use an automated tool like SQL Database Repair Software. According to the technical experts, it is the professional method to repair recovery pending state in SQL server 2012.

Final Words

A SQL server database is a lot more complex and difficult to handle. There are many glitches which occur in SQL database, and they have to be resolved as soon as possible. One such problem is a recovery pending state in SQL server. The multiple manual solutions have been described in the post to fix recovery pending state in SQL server database. Moreover, a third-party tool is also introduced to resolve the problem in a hassle-free manner.

    



From here: https://microsoft.github.io/AzureTipsAndTricks/blog/tip140.html

Note by cbattlegear One important caveat to this process (as shown below). If any writes are happening on the database while you do the export the import will be broken. Best practice is to run CREATE DATABASE AS COPY to create a copy of the database and create an export of the copy.

Right-click on the Database -> click Tasks > Export data-tier application

Now ensure you are connected to your local target SQL server instance (or SQL Azure instance) and right-click on the Database -> click Tasks > Import data-tier application and select the .backpac file that you created earlier. 



When running a Robocopy command to copy files from a Windows file location to a UNIX type location such as a NAS DO NOT use the \B switch. This will result in Access Denied because it will try to copy the Windows permissions to the NAS location adn it's not possible for obvious Reasons.



from: https://stackoverflow.com/questions/8311303/cannot-obtain-value-of-local-or-argument-as-it-is-not-available-at-this-instruct

In visual Studio 2017 goto Debug->Option then check Debugging->general-> and check this option. Suppress JIT Optimization on modual load (Managed Only)



From here: https://sharepoint-learners.blogspot.com/2016/09/sharepoint-2013-approve-list-item.html

Work Statement: Approve all the list items
 
$site = new-object Microsoft.SharePoint.SPSite("http://site")
$web = $site.OpenWeb()
$list = $web.Lists["Posts"]
$items = $list.Items

foreach ($item in $items)
{
    $item["_ModerationStatus"] = 0
    $item.Update()
}



Work Statement: You want to approve a single list item

$site = new-object Microsoft.SharePoint.SPSite("http://site")
$web = $site.OpenWeb()
$list = $web.Lists["Posts"]
$items = $list.GetItemById(10)
$item["_ModerationStatus"] = 0

$item.Update()


The following are more ModertionStatus:

ValueDescription
0 The list item is approved.
1 The list item has been denied approval.
2 The list item is pending approval.
3 The list item is in the draft or checked out state.
4 The list item is scheduled for automatic approval at a future date.



From here: https://serverfault.com/questions/57222/how-to-send-ctrlaltdel-using-remote-desktop

Ctl + Alt + End is the prescribed way to do this!



From here: https://stackoverflow.com/questions/19077356/how-to-determine-what-fields-were-update-in-an-update-trigger

create trigger utr_Table1_update on Table1
after update, insert, delete
as
begin
    with cte_inserted as (
        select id, (select t.* for xml raw('row'), type) as data
        from inserted as t
    ), cte_deleted as (
        select id, (select t.* for xml raw('row'), type) as data
        from deleted as t
    ), cte_i as (
        select
            c.ID,
            t.c.value('local-name(.)', 'nvarchar(128)') as Name,
            t.c.value('.', 'nvarchar(max)') as Value
        from cte_inserted as c
            outer apply c.Data.nodes('row/@*') as t(c)
    ), cte_d as (
        select
            c.ID,
            t.c.value('local-name(.)', 'nvarchar(128)') as Name,
            t.c.value('.', 'nvarchar(max)') as Value
        from cte_deleted as c
            outer apply c.Data.nodes('row/@*') as t(c)
    )
    insert into Table1_History (ID, Name, OldValue, NewValue)
    select
        isnull(i.ID, d.ID) as ID,
        isnull(i.Name, d.Name) as Name,
        d.Value,
        i.Value
    from cte_i as i
        full outer join cte_d as d on d.ID = i.ID and d.Name = i.Name
    where
        not exists (select i.value intersect select d.value)



From here: https://forums.asp.net/t/1740537.aspx?Microsoft+Excel+cannot+access+the+file+is+solved+by+creating+a+folder

These are the steps for resolving the issue. If I ran Task Scheduler on demand it worked, but it would not interact with Excel of it was Triggered by the timer!

Run> dcomcnfg

This will open Component Services and navigate to Console Root
\Component Services\Computers\My Computer\DCOM Config\Microsoft Excel Application
Right click Microsoft Excel Application and select Properties

Click on Identity Tab and check selection.

It works for me when select option - The interactive user



From here: https://blogs.msdn.microsoft.com/korbyp/2003/12/17/how-to-remove-a-project-from-source-control-in-visual-studio/

To (permanently) remove a project or solution from source control

1.      In Visual Studio, click File, click Source Control, and then click Change Source Control.

2.      In the Change Source Control dialog box, select the project and/or solution you want to remove from source control, and then click Unbind.

3.   Delete all solution or project files in the source control database.

4.      In Windows Explorer, locate the working directory for your solution/project and then delete all *.scc files.


Note   In Visual SourceSafe and many other SCC systems, if other users have checked out the solution/project in the past, even if they have since checked in their changes, the next time they attempt to Get or Check Out the solution/project, they will be prompted to add the items to source control from their working copies on disk. On the SourceSafe team, we call this a “pending add“, which is a little confusing. Anyway, if you want to guarantee that a solution/project is permanently, permanently, permanently removed from source control and will never again re-appear in your database, you should repeat the final step in the preceding procedure for all solution/project enlistees.



When you’re trying to open your SSIS or SSRS solution, you will get a message that the migration has failed or ‘The application which this project type is based on was not found.

From https://www.jonashendrickx.com/2017/06/26/cannot-open-ssrsssis-projects-vs2017/ 

Method 1

  1. Open ‘Microsoft Visual Studio 2017‘.
  2. In the menu bar, expand ‘Tools‘, then choose ‘Extensions & Updates‘. to install an extension.
  3. Search for ‘Microsoft Reporting Services Projects‘, and install this extension.
  4. To complete the installation, shut down all windows and instances of Microsoft Visual Studio 2017. Then the installer will start.
  5. Try to open your solution or projects (*.rptproj) again.

Method 2

  1. Close all windows and instances of ‘Microsoft Visual Studio 2017‘.
  2. Download ‘Microsoft Reporting Services Projects‘ from the Visual Studio marketplace.
  3. Open your solution or project.


The Blogger

Dave Stuart I'm a Developer with a passion for coding. I enjoy the challengers that come with the job! SharePoint is one of my expert areas along with .NET Web Development with MVC and good old MS Access VBA coding. I Blog so that I can remember how I did that way back when; PLUS all this stuff is searchable! I constantly study and run my own business, Dafran Inc. I have passed 22 Microsoft Exams since 1998 when I first jumped on the treadmill of knowledge. I hope that you enjoy this Blog as much as I enjoy updating it. All the very best from Calgary, Alberta, Canada. contact me at linkedin @ dafran.ca

Calendar

<<  January 2020  >>
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
272829303112
3456789

View posts in large calendar

Sign in