Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Database in 'Recovery Pending' State -Error 945 Expand / Collapse
Author
Message
Posted Tuesday, December 27, 2011 12:41 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 8:57 AM
Points: 219, Visits: 721
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.


Post #1227066
Posted Tuesday, December 27, 2011 12:56 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1227073
Posted Tuesday, December 27, 2011 1:08 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:19 AM
Points: 43,026, Visits: 36,192
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 2008, MVP
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

Post #1227079
Posted Tuesday, December 27, 2011 1:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 8:57 AM
Points: 219, Visits: 721
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

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?
Post #1227083
Posted Tuesday, December 27, 2011 1:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 8:57 AM
Points: 219, Visits: 721
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.
Post #1227084
Posted Tuesday, December 27, 2011 1:25 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:19 AM
Points: 43,026, Visits: 36,192
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 2008, MVP
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

Post #1227085
Posted Tuesday, December 27, 2011 1:27 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:19 AM
Points: 43,026, Visits: 36,192
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 2008, MVP
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

Post #1227086
Posted Tuesday, December 27, 2011 1:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 8:57 AM
Points: 219, Visits: 721
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?
Post #1227088
Posted Tuesday, December 27, 2011 1:33 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1227089
Posted Tuesday, December 27, 2011 1:56 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:19 AM
Points: 43,026, Visits: 36,192
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 2008, MVP
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

Post #1227093
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse