December 29, 2011 at 8:25 am
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
December 29, 2011 at 8:28 am
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.
December 29, 2011 at 8:37 am
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.
December 29, 2011 at 8:54 am
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
December 29, 2011 at 8:58 am
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
December 29, 2011 at 9:52 am
Besides understanding backups better, you might want to figure out how to be more proactive in making sure you know when something is failing.
SQL files might not be the only things failing with file in use errors if you look at the event log.
When we used Tivoli, we by design, excluded mdf and ldf files, knowing that unless we stopped SQL, they would fail.
Hopefully your data loss is minor and you can use this as a learning experience.
Part of your disaster recovery plan should include testing to be sure you have conficence that you are not missing anything.
Just for reference, we get email notifications on failures written to the event log. So we would be notified, and not have to rely on a manual check.
Our backups go to separate disk (SAN), which has some additional redundancy.
These backups also get backed up through a product like Tivoli.
And additionally, this other backup gets written to tape and taken offsite after a couple of days.
This may seem like overkill, but might be fairly typical of what others do.
December 29, 2011 at 10:51 am
This was a server that one of our customers purchased and never told us about until a week ago. It was not even located in our datacenter. They are just reaching out to us for help now...after the fact. If this was one of my servers i probably wouldn't be typing this right now :ermm:
December 29, 2011 at 10:54 am
Vertigo44 (12/29/2011)
This was a server that one of our customers purchased and never told us about until a week ago. It was not even located in our datacenter. They are just reaching out to us for help now...after the fact.
In which case, you need to remember that their emergency is not necessarily your emergency. Yes, you want to give good customer service, but step back for a moment, take a deep breath, and relax.
Then prepare to give them bad news.
December 29, 2011 at 11:24 am
GilaMonster (12/29/2011)
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.
I meant to say 'creating a new LDF file'. Sorry.
December 29, 2011 at 11:38 am
Forget about creating a new .LDF file. You can't do anything right now anyway except follow the steps as they were given you.
December 29, 2011 at 11:42 am
At first I was pretty nervous but i'm settled down now and view it as more of a challenge at this point. Plus i'd really like to come through for them and get their data back.
I'm going to try the Emergency Repair Option soon.
December 29, 2011 at 11:59 am
When running DBCC CHECKDB (emergencydemo, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
is there a way to see what tables were lost or at least some information on what happend...
Also, out of curiousity if we are able to recover the .LDF file from the bad drive after running
DBCC CHECKDB (emergencydemo, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
would we have the ability and go back and try and bring the database online using the recovered .LDF file?
In other words should I wait to find out if we have a .LDF to use?
Please note that the database .MDF file is currently saying its in use and is unavailabe for us to copy it and make a backup. We are trying to figure out how to get a copy of the MDF file before we move in any direction.
If we were to shutdown the database server or stop the database service in order to release the MDF file would the database still come back up in its current 'Recovery Pending' state? Would that be safe to do in order to get a copy of the MDF file?
December 29, 2011 at 12:19 pm
Vertigo44 (12/29/2011)
When running DBCC CHECKDB (emergencydemo, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;is there a way to see what tables were lost or at least some information on what happend...
Maybe, it reports on what it does if it's fixing corruption, but in this case it's more about what data never made it into the mdf, and that is unknown (it's only recorded in the ldf, which you don't have)
Also, out of curiousity if we are able to recover the .LDF file from the bad drive after running
DBCC CHECKDB (emergencydemo, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
would we have the ability and go back and try and bring the database online using the recovered .LDF file?
Not if you've already run repair. The only way the ldf might be usable is if you don't run repair, leave the DB RECOVERY_PENDING and then replace the D drive with the ldf where SQL thinks it should be and see if the DB comes up. It's a slim chance, I don't recall having seen a database file recovered from a disk having been usable.
If we were to shutdown the database server or stop the database service in order to release the MDF file would the database still come back up in its current 'Recovery Pending' state? Would that be safe to do in order to get a copy of the MDF file?
Yes, you can stop SQL and then copy the file out and restart SQL. DO NOT attempt to take the database offline. DO NOT detach the 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 30, 2011 at 9:48 am
Ok after shutting down the SQL service and trying to copy the MDF file out I will still not able to b/c the database is still attached. So to get around this we booted into a Pre-execution (PE boot disk) ...linux I believe and grabbed the MDF that way. So now we have a copy of the MDF as it was...
Moving forward.. I have kicked off the following...
ALTER DATABASE emr SET EMERGENCY;
GO
ALTER DATABASE emr SET SINGLE_USER;
GO
DBCC CHECKDB (emr, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
And checked the state of the database while watching the logs... using this..
SELECT state_desc FROM sys.databases where name='emr';
GO
The state does say its ONLINE now but the DBCC REPAIR query is still processing after 15minutes... I also don't see anything in the results window or the messages window. Is it okay to set it to multi_user now? Or am I waiting for something? The database is about 60GB so I'd imagine I'd have to wait a while...
December 30, 2011 at 9:58 am
select percent_complete from sys.dm_exec_requests
shows it's 56.85% complete
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply