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 2:03 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
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!
Post #1227095
Posted Tuesday, December 27, 2011 2:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 39,874, Visits: 36,217
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 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 #1227096
Posted Tuesday, December 27, 2011 2:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 39,874, Visits: 36,217
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 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 #1227097
Posted Tuesday, December 27, 2011 3:08 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 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...
Post #1227121
Posted Thursday, December 29, 2011 8:06 AM
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
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?
Post #1227802
Posted Thursday, December 29, 2011 8:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 39,874, Visits: 36,217
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 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 #1227813
Posted Thursday, December 29, 2011 8:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:20 AM
Points: 1,259, Visits: 3,567
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.
Post #1227815
Posted Thursday, December 29, 2011 8:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:13 AM
Points: 5,573, Visits: 6,357
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1227829
Posted Thursday, December 29, 2011 8:54 AM
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
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
Post #1227844
Posted Thursday, December 29, 2011 8:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 39,874, Visits: 36,217
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 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 #1227847
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse