September 28, 2014 at 6:52 am
I know i am posting this very late but might be it will helpful for someone.
I Faced the similar issue in my production environment after schedule patching reboot activity and I fix this by.
1- Tried SQL Server service-- Database came back online but some other database went into "Recovery Pending" state.As i Have more than 180 Content DBs hosted on that instance.
Select * from sys.databases where state <>0
2- Detach the database from SQL Server and tried to reattach again but it was failing because SQL Server could not read the file from disk and throwing msg that its in use.
So i check for the possible processes which might lock the file and kill the process. (In my case it was tape backup holding the mdf file).
3-After killing process I re-attach the DB and boom it worked for me.
September 28, 2014 at 7:41 am
All you had to do was kill the tape backup task, offline the DB and bring it online again. Detaching wasn't at all necessary. If you'd looked in the SQL error log, you'd have seen messages saying that SQL couldn't open the mdf because of OS error 112 (file in use by another process), which is a very good hint that some other process has the file locked. The backup process in this case.
p.s. those backup tasks should skip SQL database files, for exactly this reason
p.p.s. That solution was specific to your problem, not a general one for any recovery_pending database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 28, 2014 at 11:54 pm
Well Gila Sir is right you need to tape backup task , offline the DB and bring it online again
February 5, 2015 at 5:05 am
Thanks a lot!!! This worked for me!!!!
February 5, 2015 at 5:10 am
Thanks! It was the only thing that worked!!!
March 2, 2015 at 8:24 am
I was able to take the offending DB off line and bring it back online to force the server to do the recovery. Seems like its all good now...still testing 🙂
April 22, 2015 at 8:22 pm
If your database is in Recovery Pending mode due to any reason, don't worry execute the following code you can fix it.
ALTER DATABASE database_Name
SET EMERGENCY;
Go
ALTER DATABASE Database_Name
SET SINGLE_USER
USE MASTER
dbcc checkdb('Database_Name',Database_Name, REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE Database_Name
SET MULTI_USER
Rajendra Gautam
SQL Developer
Dominion Enterprises
April 23, 2015 at 2:24 am
gautamraj50 44895 (4/22/2015)
If your database is in Recovery Pending mode due to any reason, don't worry execute the following code you can fix it.ALTER DATABASE database_Name
SET EMERGENCY;
Go
ALTER DATABASE Database_Name
SET SINGLE_USER
USE MASTER
dbcc checkdb('Database_Name',Database_Name, REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE Database_Name
SET MULTI_USER
No, it won't fix a database in Recovery_Pending mode (it's for a DB in SUSPECT), and 'don't worry' is a really silly thing to say right before telling people to run a command which will result in data loss!
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2015 at 11:51 pm
For me, just restarting the sql serve fixed the problem. After restart the recovering could be done.
I think that this is the first step to be done when facing the "Recovery pending" issue.
June 10, 2015 at 1:34 am
shaulbel (6/9/2015)
I think that this is the first step to be done when facing the "Recovery pending" issue.
The first step has to be to identify why the databases are in recovery pending. A restart may fix it or may do nothing, depending on the root cause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 17, 2015 at 11:27 am
This worked perfectly for me. Thanks
August 21, 2015 at 5:36 am
This was removed by the editor as SPAM
September 29, 2015 at 6:57 am
recovery is in pending mode it means recovery is not completed due to some problem. if you are unable to figure out reason then then get expert help. use any other recovery tool to continue the process.
Visit here for more help :- http://en.mssqldatabaserepair.org
October 14, 2015 at 2:48 am
Just to add I had this problem on a test server where the log disk had been replaced with a larger disk. Manually restarting SQL Server resolved the issue.
December 3, 2015 at 7:58 am
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?
Viewing 15 posts - 31 through 45 (of 67 total)
You must be logged in to reply to this topic. Login to reply