October 23, 2012 at 12:21 pm
I have a spid in SQL SERVER 2012 that has been running for 4 days on a RESTORE VERIFYONLY FROM DISK for the master database. I killed the spid but when I run KILL 76 WITH STATUSONLY I get the following output:
"SPID 76: transaction rollback in progress. Estimated rollback completion: 53%. Estimated time remaining: 372821 seconds."
Each time I run the WITH STATUSONLY the Estimated time remaining keeps going up and the percentage stays the same.
I verified that the BAK file is still on disk that it is trying to verify. I did a select * from sys.sysprocesses where spid = 76 and there are two entries. There are two different entries for kpid and two different wait types (BACKUPTHREAD and BACKUPIO) for each of the spid 76.
I have found a connect article that seems to refer to this problem, but is listed for previous version of SQL Server.
Since this is a backup process and doing verify only is there something else I should be looking for to kill the spid?
If I restart the instance will this kill it and would it cause a problem with the master database since it is doing a VERIFYONLY?
October 23, 2012 at 3:15 pm
Process Monitor (ProcMon) from sysinternals may be able to kill the thread to backup file. Restarting SQL will definitely kill it and may be your only recourse if ProcMon doesn't work.
Backup VerifyOnly doesn't do anything to any live databases. It will have no effect on master.
October 24, 2012 at 12:08 pm
I gave a quick call to Microsoft to check on the connect "Bug" that I found and they said it was not a bug. I had to cycle the instance.
Just cycling the services did not correct the issue so I had to boot the box. The process was removed and I'm able to take backups again.
Thanks Robert!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply