December 27, 2011 at 12:41 pm
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.
December 27, 2011 at 12:57 pm
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.December 27, 2011 at 1:08 pm
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
December 27, 2011 at 1:21 pm
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?
December 27, 2011 at 1:24 pm
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.
December 27, 2011 at 1:25 pm
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
December 27, 2011 at 1:27 pm
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
December 27, 2011 at 1:31 pm
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?
December 27, 2011 at 1:33 pm
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.December 27, 2011 at 1:56 pm
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
December 27, 2011 at 2:03 pm
Yes MDF and LDF from different times makes sense...
Good news is that even though SQL Agent history and job history didn't show any EMR db backup history we did find .BAK and Lots of .TRN backups on the TSM backup of the D: Drive that we lost!!! I don't want to celebrate too soon but I think we just dodged a bullet big time! 😀
December 27, 2011 at 2:04 pm
PaulB-TheOneAndOnly (12/27/2011)
Check the post dated 5/16/2009 12:02;17am on this thread: http://www.sqlservercentral.com/Forums/Topic717382-266-2.aspx
No. Please no. As Paul said (in that thread)
that is NOT the supported/documented approach and is only suggested when EMERGENCY mode repair does not 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
December 27, 2011 at 2:04 pm
Vertigo44 (12/27/2011)
Good news is that even though SQL Agent history and job history didn't show any EMR db backup history we did find .BAK and Lots of .TRN backups on the TSM backup of the D: Drive that we lost!!!
Do you know when they are from? Are they restorable?
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
December 27, 2011 at 3:08 pm
I found out that since the LDF file was in use during the TSM backup, the (.LDF) file was skipped. This means no recovery to point of failure. However, I do have backups from 12/7/11. Although quite a bit older than I hoped for it's better than the WITH DATA LOSS option... We are now moving the TSM backup to a new drive for testing and will resume in the morning. I am heading home on stand by... Thank you EVERYONE for being there! I guess this counts as my first Fire Alarm as a DBA!! Just when I was getting used to calm days filled with interesting reading of BOL, Brad McGee, and Rod Colledge...
I will update in the morning...
December 29, 2011 at 8:06 am
Bad news guys!
Well after having a closer look the .BAK files and the .TRN files we thought we had are in fact a year old. Now, what I am wondering is that if I run checkDB with the repair_allow_data_loss option, how much data will I loose? Am I correct in saying that it would be better to run the 'allow data loss' and recreate a new empty LDF vs restoring a 1 year old backup. Please correct me if I am wrong but the way I understand transactions to work is that transactions are immediately applied to the MDF file while the LDF logs that transaction. Transactions are not stored up in the LDF file waiting to be applied to the MDF file on an interval / batch process.?? Correct?
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply