Database in Recovery Pending State
-
Velveeta22 (12/3/2015)
GilaMonster (6/8/2009)
Recovery pending means that for some reason SQL cannot run restart recovery on the database. Usually this is because the log is missing or corrupt."G:\Data\MSSQL\Database.mdf".
"G:\Data\MSSQL\Data\Database_log.ldf"
Do these files exist? Is the drive online and available? If so, try stopping and restarting the SQL service as it may be that SQL came up before the drive was available. That would have caused this error.
Best advice of the thread, thank you Gila. Worked like a charm.
I have also noticed a consistency in the thread. Many are dealing with failed Windows\SQL Server updates who experience this issue, which is true in my case. I have two sister servers. One has never had an issue with failed updates, the other I have many failed updates, WMI issues and most databases will not come back online after quarterly reboots. The databases come back online with no issues on the sister server that has never had a failed update.
Anyone take a shot in the dark on the root cause going on here?
It is possible that your WMI repository is corrupt, this would cause update failures. If you follow the following article it will explain how to re-build it
http://blogs.technet.com/b/askperf/archive/2009/04/13/wmi-rebuilding-the-wmi-repository.aspx
_________________________________________________________________
"The problem with internet quotes is that you cant always depend on their accuracy" -Abraham Lincoln, 1864
-
Great suggestion, thanks for the link. I have checked for WMI corruption using the steps in the link and everything checks out fine.
Below are a few scenarios/errors that indicate repository corruption:
- Unable to connect to root\default or root\cimv2 namespaces thru wbemtest. Fails returning error code 0x80041002 pointing to WBEM_E_NOT_FOUND
- When we open Computer Management and Right Click on Computer Management(Local) and select Properties, you get the following error: "WMI: Not Found" or it hangs trying connect
- 0x80041010 WBEM_E_INVALID_CLASS
- Trying to use wbemtest, it hangs
I also ran WMIDiag and am getting WBEM_E_ACCESS_DENIED for a domain user that has admin priv on the server and sysadmin on the sql server. Possible issue here?
-
SFC.exe /verifyonly came back with no corrupt system files.
-
Ran the Windows Update Troubleshooter: http://go.microsoft.com/?linkid=9830262
It found 2 issues and fixed those issues automatically, one of which was:
0x80070005 -2147024891 ERROR_ACCESS_DENIED The authentication method is not supported. - CoCreateInstance(IBackgroundCopyManager) fails with E_ACCESSDENIED (0x80070005) in this condition
I cannot reboot and install the remaining failed updates until the next maintenance window, but fingers crossed this was the fix. Thank you everyone for your help.
-
glad you resolved it
_________________________________________________________________
"The problem with internet quotes is that you cant always depend on their accuracy" -Abraham Lincoln, 1864
-
Corrupt NDF file had no data so removed it and the database will back online
-
I also had this issue today with my DEV DB server, and database was showing in Recovery Pending State.
On running the mentioned statement in one of previous responses, the database came back to normal:
ALTER DATABASE dbXYZ SET ONLINE WITH ROLLBACK IMMEDIATE
-
manub22 (2/13/2016)
On running the mentioned statement in one of previous responses, the database came back to normal:ALTER DATABASE dbXYZ SET ONLINE WITH ROLLBACK IMMEDIATE
That only works after whatever caused the RECOVERY_PENDING state has been fixed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
ericjorg (4/9/2013)
I had this issue on Sql 2012 and fixed it this way:1) Stop SQL
2) Delete the recovering database's log file
3) Start SQL
That worked for me. Thanks a lot!
-
I have been running the same issue in 2016 Evaluation version on Hyper-V Machine. This is resolved by just restarting the SQL Services.
Regards
Baig
-
GilaMonster (2/14/2016)
manub22 (2/13/2016)
On running the mentioned statement in one of previous responses, the database came back to normal:ALTER DATABASE dbXYZ SET ONLINE WITH ROLLBACK IMMEDIATE
That only works after whatever caused the RECOVERY_PENDING state has been fixed.
An example of this:
We had a data drive and TLog drive fill up on an old dev server which put one of the databases in RECOVERY PENDING.
Once some space was cleared in the databases, files shrunk etc, I ran the above statement to recover the situation.
-
Great ! It Works ! Thanks
-
take offline, then bring online, worked for me.
-
HI, i have a similar problem. One of our database went on Recovery mode and after few hours the database became normal mode. but when we try to access the tables through application, there are few tables is giving error message saying the following message.
"The database snpshop for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes des not support spare files or alternate streams. attempting to get exclusive access to run chekcs offline
he daa base couldn not be exclusively locked to perform the operation.. Check statement aborted. The database couldn not be checked as a database snapshop could not be created and the database or table could not be locked. See books online for details of when this behaviour is expected and what worksrounds exist .
"
can you kindly help.
-
This worked for me, thanks
Viewing 15 posts - 46 through 60 (of 67 total)
You must be logged in to reply to this topic. Login to reply