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
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! :-D
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: 47427 Visits: 44405
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

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: 47427 Visits: 44405
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

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 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...
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
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?
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: 47427 Visits: 44405
Vertigo44 (12/29/2011)
Now, what I am wondering is that if I run checkDB with the repair_allow_data_loss option, how much data will I loose?


Anywhere between nothing and everything. Seriously, there's no way to answer that. You could lose nothing. You could lose a couple hundred rows with no other effects. You could lose just a few rows yet have major transactional inconsistencies afterwards (data belonging to uncommitted transactions could have been written while some data from committed transactions could have not). You could end up with schema inconsistencies resulting in a DB that's not usable. Repair could fail entirely meaning you've lost everything.

The only way to tell is to run it and then spend time (likely lots of time) investigating the effects.

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.


Not quite. Log records are written immediately, the data changes are written from memory (not from the log file) to the data file later. It could be a few ms later, it could be a few seconds later.


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


calvo
calvo
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1380 Visits: 3965
Sorry to hear about your debacle, hopefully someone can learn from this situation.
Transactions are not written to the data file immediately (define immediately?) although it happens relatively quick.
I don't think you can determine beforehand how much data will be lost with ALLOW_DATA_LOSS, but I must defer to an expert.

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Brandie Tarvin
Brandie Tarvin
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7828 Visits: 8759
Just to append for future readers:

A backup of a drive where the .ldf, .mdf, or .ndf files live is not a SQL Server backup. From my experience, if the backup was taken while the database was ONLINE and the services were running, the file will be unreadable and unusable for emergencies like this one. So, even if you had data files and log files from the same time, it's doubtful your D: drive backup would have worked out for you.

The only way such file backups are ever usable (again, in my experience) is if they were taken when the database was OFFLINE or the sql services had been stopped. And even then, this is a really bad backup option.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
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
Is there a way for me to tell if the database was cleanly shutdown or not? I know that the state is 'RECOVERY PENDING' since the LDF file is missing.

I want to make sure I do things in the right order to better my chances of restoring the MDF file and creating a new MDF file. Will the following work if the database was not cleanly shutdown?

ALTER DATABASE emergencydemo SET SINGLE_USER;
GO
DBCC CHECKDB (emergencydemo, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

This is what I am referencing to get this mdf back online.... http://www.sqlskills.com/blogs/paul/post/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort.aspx
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: 47427 Visits: 44405
Vertigo44 (12/29/2011)
Is there a way for me to tell if the database was cleanly shutdown or not? I know that the state is 'RECOVERY PENDING' since the LDF file is missing.


I can tell you outright that is was not shut down cleanly. If it had been, it would not be in recovery pending right now. Also, straight from the error log that you posted:
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.


I want to make sure I do things in the right order to better my chances of restoring the MDF file and creating a new MDF file.


You're not trying to create a new MDF

ALTER DATABASE emergencydemo SET SINGLE_USER;
GO
DBCC CHECKDB (emergencydemo, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO


I already posted the steps for you.

GilaMonster (12/27/2011)
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.


That's the exact steps that Paul's blog post gives too.


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