January 16, 2012 at 4:56 am
Hi - we have an issue where a T-log backup failed and ended up in a hung state and the spid was killed. This is now rolling back however it is saying it could take 35 hours.
We are thinking about restarting SQL but could anyone comment on what impact this would have on the rollback?
January 16, 2012 at 6:16 am
Depends on the edition you are running. Enterprise edition has a feature called "fast recovery", that other editions don't have.
In normal recovery, the database becomes available when the recovery process has finished. Recovery means roll forward of committed transactions and roll back of uncommitted transactions.
In fast recovery the database becomes available when the roll forward part has finished. The uncommitted transactions are rolled back with the database online, locking rows to rollback.
That said, it's quite unlikely that the rollback completes in a quicker fashion if you restart the instance and, depending on your edition, you could end up locking the whole database rather than just the objects affected by the rollback operation.
In a few words: don't do it.
-- Gianluca Sartori
January 16, 2012 at 6:29 am
Thanks for the reply. We are using SQL 2008 SP1 Enterprise edition.
I have been reading here however where people have suggested a restart is needed:
http://connect.microsoft.com/SQLServer/feedback/details/433703/killed-rollback#details
However this is further complicated as its a Litespeed backup thats rolling back.
Furthermore we haven't dared to restart the log backups as the rollback is still happening. Is it safe to restart the log backups in this state?
January 16, 2012 at 6:40 am
My bad, I didn't read your question carefully.
Now I see that you're dealing with a hung backup...
Well, if possible, a restart will probably fix it.
What does SQLServer log say? Is there any meaningful entry for this error?
-- Gianluca Sartori
January 16, 2012 at 6:41 am
I've seen this twice before and heard about it another time. In all cases a restart was required. Check that the rollback isn't blocked by anything first.
As for the time rolling back, log backups don't make huge changes to the database (log header, VLF headers and DB boot header is about all), so there's little to roll back. You can try taking another log backup, but my guess is that you won't be able to (it'll be blocked by the one that's rolling back). you may not even be able to take full backups (couldn't in the one case I had)
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
January 16, 2012 at 7:03 am
Hi - thanks for the replies.
Yes we tried the log backup but as you stated it was blocked by the rollback. At the time the log backup failed there was the following in the log file.
BackupIoRequest::ReportIoError: write failure on backup device 'VDI_36F7FC95-94C6-4468-9877-301F68077C55_13'. Operating system error 64(failed to retrieve text for this error. Reason: 15105).
following this was a lot of dump information and the error:
SQL Server Assertion: File: <bckvd.cpp>, line=2046 Failed Assertion
We are currently restoring a full backup we managed to take after this happened and run a CHECKDB against it.
We will be performing a restart of SQL tonight, as currently users still have access to the system.
January 18, 2012 at 2:33 am
Just so you know the restart of the sql server fixed this.
Quest suggested that the litespeed dll could have been held which is why the log backup did not roll back, however we have no proof of this.
January 18, 2012 at 2:44 am
Thanks for your feedback!
-- Gianluca Sartori
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply