Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database in 'Recovery Pending' State -Error 945


Database in 'Recovery Pending' State -Error 945

Author
Message
Vertigo44
Vertigo44
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 825
From a wise member: "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."

We just lost 2/5 of our hard drives which were on a Raid 5. Oddly enough, when I logged into the server to look at the datafiles all databases had their MDFs and LDFs except one database. 'Mydatabase' was missing it's LDF file after loosing the drives. I was hoping to get some advice on what comes next...

We are currently checking our Tivoli backups to see if we have a backup of the drive that had the LDF on it. But what if we come up short? Then what? I read that I should not nessassarly detach the database just yet. I am going through the logs now to see the exact error but I'm sure it has something to do with the fact we lost two drives. How will I recovery the database without the LDF?

2011-12-26 19:31:12.40 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2011-12-26 19:31:12.82 spid14s Starting up database 'ReportServer'.
2011-12-26 19:31:12.82 spid17s Starting up database 'msdb'.
2011-12-26 19:31:12.82 spid15s Starting up database 'ReportServerTempDB'.
2011-12-26 19:31:12.82 spid19s Starting up database 'MEDISPAN'.
2011-12-26 19:31:12.82 spid20s Starting up database 'HPI2010_911FIX'.
2011-12-26 19:31:12.82 spid18s Starting up database 'FORMULARY'.
2011-12-26 19:31:12.82 spid16s Starting up database 'EMR'.
2011-12-26 19:31:12.82 spid22s Starting up database 'HPI2010_SDC102'.
2011-12-26 19:31:12.82 spid21s Starting up database 'CDS_Aggregator'.
2011-12-26 19:31:12.82 spid23s Starting up database 'CDS_Aggregator_Archive'.
2011-12-26 19:31:13.26 spid16s Error: 17207, Severity: 16, State: 1.
2011-12-26 19:31:13.26 spid16s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\EMR_log.ldf'. Diagnose and correct the operating system error, and retry the operation.
2011-12-26 19:31:13.32 spid16s File activation failure. The physical file name "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\EMR_log.ldf" may be incorrect.
2011-12-26 19:31:13.32 spid16s The log cannot be rebuilt because the database was not cleanly shut down.
2011-12-26 19:31:13.68 spid17s Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required.
2011-12-26 19:31:13.73 spid19s CHECKDB for database 'MEDISPAN' finished without errors on 2011-01-19 01:45:29.630 (local time). This is an informational message only; no user action is required.
2011-12-26 19:31:13.81 spid23s CHECKDB for database 'CDS_Aggregator_Archive' finished without errors on 2011-05-18 01:37:50.367 (local time). This is an informational message only; no user action is required.
2011-12-26 19:31:13.82 spid20s CHECKDB for database 'HPI2010_911FIX' finished without errors on 2010-07-07 01:41:05.070 (local time). This is an informational message only; no user action is required.
2011-12-26 19:31:13.89 spid21s CHECKDB for database 'CDS_Aggregator' finished without errors on 2011-05-18 01:37:49.447 (local time). This is an informational message only; no user action is required.
2011-12-26 19:31:14.07 spid18s CHECKDB for database 'FORMULARY' finished without errors on 2011-08-17 01:21:41.203 (local time). This is an informational message only; no user action is required.
2011-12-26 19:31:14.42 spid22s CHECKDB for database 'HPI2010_SDC102' finished without errors on 2010-07-23 01:33:19.953 (local time). This is an informational message only; no user action is required.
2011-12-26 19:31:14.59 spid5s Recovery is complete. This is an informational message only. No user action is required.
2011-12-26 19:31:17.32 spid51 Using 'xpsqlbot.dll' version '2005.90.1399' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2011-12-26 19:31:17.64 spid51 Using 'xpstar90.dll' version '2005.90.1399' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2011-12-26 19:31:17.84 spid51 Using 'xplog70.dll' version '2005.90.1399' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
2011-12-26 20:14:05.20 Logon Error: 18456, Severity: 14, State: 16.
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3093 Visits: 4639
Do you have a good backup of the affected database?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44391
Do NOT detach, you won't be able to reattach it.

Best bet - restore from backup (and I don't mean a file backup of the ldf). Restore full, any differentials and then all log backups up to the last one run before the drive failure.

p.s. There's a maintenance problem with several of your databases, some haven't had consistency checks for over a year. That means you have no idea if there's any corruption or, if there is, when it happened. Not a safe situation.

p.p.s. There's also a update problem, the server appears to still be on SQL 2005 RTM. Latest service pack is SP4. There were some important fixes in SP1 and more in SP2. RTM is no longer supported.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Vertigo44
Vertigo44
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 825
I forgot to mention that we don't have any backups (Vendor just installed everything and I see where they created two maintenance plans 1. System DB backups which is working and 2. EMR DB backup which never ran successfully Sad

The LDF file was on D: Which is corrupt (we are sending it off to be un-corrupted)

The MDF file is on G: which is accessible

Being that there were no successful backups, wouldn't this mean that all transactions from the beginning are in the LDF and were never commited to the MDF? Please correct me if I am wrong.

The present size of the MDF is 60GB (I'm guessing an inital data load?)

Could I copy the MDF to another server and to a restore with ALLOW DATA LOSS (http://www.sqlskills.com/blogs/paul/post/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort.aspx) in the meantime and repoint the app while we try and recover the LDF off the bad drive?
Vertigo44
Vertigo44
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 825
Thanks Gila,

I believe this is a new installation and is only a few months old. (Oct or Nov of this year)

I was never notified of the server being installed and was not made aware that it was still at RTM. This is getting worse and worse.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44391
Vertigo44 (12/27/2011)
Being that there were no successful backups, wouldn't this mean that all transactions from the beginning are in the LDF and were never commited to the MDF? Please correct me if I am wrong.


You're wrong. A backup is a backup, a copy of the data to another location. Nothing whatsoever to do with transactions in the log

Could I copy the MDF to another server and to a restore with ALLOW DATA LOSS (http://www.sqlskills.com/blogs/paul/post/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort.aspx)


It's about the only option you have left. I wouldn't bet much (if anything) on the uncorrupting of the drive. I've never seen a mdf, ndf or ldf recovered that way having been usable afterwards.

Switch the database to emergency mode, then to single user mode, run checkDB with the repair_allow_data_loss option. It will lose data, it may not work. If it doesn't work, there's no further alternatives, this is the very last resort.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44391
Vertigo44 (12/27/2011)
I believe this is a new installation and is only a few months old. (Oct or Nov of this year)


There are databases on there that date to July last year, or before, so it's either much more than a few months old, or older databases were restored or attached.

CHECKDB for database 'HPI2010_911FIX' finished without errors on 2010-07-07 01:41:05.070 (local time). This is an informational message only; no user action is required.


That's the last time CheckDB was run on that 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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Vertigo44
Vertigo44
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 825
Update!!

We did find a backup of the D drive on our TSM storage server. I am checking now to see if the LDF file is there and if we can create enough space on the server to restore to a new drive.

Now I am wondering if we restore the backup of the D: Drive to our server as a new drive letter assuming we can't call it D: (maybe we can), how would I tell the database server to look for the recovered LDF on the new drive location?
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3093 Visits: 4639
Check the post dated 5/16/2009 12:02;17am on this thread: http://www.sqlservercentral.com/Forums/Topic717382-266-2.aspx

Edit: Sorry - I was reseaching this when you updated the thread with the news that a backup actually exists.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47259 Visits: 44391
I hate to be the bearer of bad news, but you can't mix and match ldf and mdf files from different times.

To get that DB up, you'd need mdf and ldf from exactly the same time (which is why file backups of database files don't usually work). An ldf that's newer than the mdf may work (providing it's not a huge time difference, so maybe a few seconds), an ldf that's older than the mdf won't work


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search