July 5, 2012 at 3:43 am
Hello,
Is anyone can help on this?
After launching a manual full backup, I lost my connection on SQL manager Studio. I had to kill spid that ran the backup But the process is still stuck on state=SUSPENDED and Command=killed/rollback. if I manually try to kill :
KILL 59
I have this message that still the same qince
"SPID 59: transaction rollback in progress. Estimated rollback completion: 79%. Estimated time remaining: 18139 seconds."
Is there a way to fix this without restart the server?
Thanks in advance
July 5, 2012 at 11:36 pm
No need to restart the server, just check is there any blocking in server.
July 7, 2012 at 11:09 am
Please check whether the Percentage of restore is happening using
Select percent_complete from Sys.dm_exec_requests where session_ID = --Mentioned the SPID which is in Killed rollback
If the percentage is going up, then probably you need to restart the SQL Services, Sometimes SQL restart does not clear the Killed/Rollback state, As a last resort you need to restart the Server for clearing the Killed/Rollback state.
Thanks
S.R.Kundur
July 13, 2012 at 2:20 am
Many thanks for your response,
At the end I restarted SQL Server, and the transaction gone, fortunaltely.
But I learn from this post !
July 14, 2012 at 8:14 am
ok this is really interesting, how does a backup database command get associated with a backup database operation? I mean how does a transaction come in to the picture during a database backup.
you also mentioned that the problem went away after the server was restarted.
were you able to identify the databaseid associated with the SPID that was in the rollback process after it was killed, and was that dbid pointing to the same database which was being backed up? it would really useful if you can confirm the same.
The only reason i can think of as of now is that the backup database may be making use of a database snapshot as part of its entire operation and then for the backup to be consistent to the time the backup completed it is probably trying to apply the log changes since the backup started.
and may killing the SPID results in rolling back of the database snapshot creation and when you restart the server the database snapshot may be getting dropped immediately.
Just wild-guessing over here but logically speaking there has to be some transaction in progress and that transaction surely cannot be in the database that is being backed up.
we all know that DBCC CheckDB uses internal snapshot( as per Paul Randal's blog), does DBCC CheckDB use something similar to minimize impact on the concurrent operations?
would be thrilled to see SQL Server Gurus shed some light on this.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply