March 13, 2014 at 7:29 am
Last week we had a power outage at work. It was 4:00 in the morning, the UPSs lasted for about 15 minutes then everything went off. When the power came back on (at 9:00) and we booted everything back up. 2 of my databases were listed as suspect. I tried a couple of things then realized I just needed to restore from backup. I have local full and transactional backups, tape backups and log shipping. When I tried to restore the last transaction logs on the local drive they failed. I ended up copying the files from the log shipping server and they restored without issue.
I was just wondering what could have caused this (suspect status and log restore failure) and why only 2 of the databases? We were thinking it could have been because of the sudden shut down and things from memory not being written to disk. If that is the case what could be done to prevent this from happening in the future, besides being available to shut down the server properly.
Details : SQL Server 2008 R2
Server 2012 (Virtual)
Raid10 SSD
Thanks for any insight.
Tracie
March 13, 2014 at 7:48 am
Suspect status occurs when SQL encounters corrupt or damaged pages in the database during a rollback or crash recovery. It could be that some of the writes which SQL had been told had completed did not make it to disk when the power failed and when SQL restarted it accessed the partially written pages as it recovered the database.
Nothing to do with stuff in memory not being written to disk
Check your IO subsystem, make sure that any write caches are battery-backed to avoid lost writes in the case of a power failure. If there are any write caches which are not battery-backed, they need to be disabled. Make sure drivers and firmware is up to date.
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
March 13, 2014 at 7:57 am
Thanks Gail. I do not know too much about that side of things, but I will pass it on to the person that does.
Tracie
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply