November 8, 2010 at 7:59 am
Hello,
I have a SPID that was used in a transaction log backup. That SPID was killed because the backup job was taking too long. It was killed four days ago. Below is the wait type for the session that is in killed/rollback. The session is showing SUSPENDED
session_idexec_context_idwait_duration_mswait_type
76 0 714923503BACKUPTHREAD
76 1 714923291BACKUPIO
"kill 76 with statusonly" gives me.
SPID 76: transaction rollback in progress. Estimated rollback completion: 72%. Estimated time remaining: 273312 seconds.
And the remaining seconds value is just increasing. Completion % hasn't changed.
While I figure I could restart the SQL instance and clear this up, but I would rather understand what is going on. The server is a cluster. The backup storage is SAN storage. There are several databases on this server that are not having any problems with backing up except for one. That one, call it DatabaseX, still shows that it is doing a transaction log backup. I can't remove the trn file associated with this killed backup as it "is in use" by the SQLSRVR process [verified with Process Explorer].
I've not been able to locate much about the wait types, other than their description. Have any of you seen this before and possibly know what is causing that wait?
-Joseph
November 21, 2010 at 9:30 am
I have seen the same issue in SQL 2005. When you backup tlog, SQLServer commits the transactions and once commited it can't be rollback.
November 21, 2010 at 11:12 am
EvilDba (11/21/2010)
When you backup tlog, SQLServer commits the transactions
Err.. No.
Backing up the transaction log does not commit any transactions. The only thing that commits a transaction is the COMMIT TRANSACTION statement in the outermost transaction (that's for implicit or explicit transactions, for auto-commit, it's the completion of the statement)
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
November 21, 2010 at 11:15 am
j mills (11/8/2010)
Have any of you seen this before and possibly know what is causing that wait?
I've seen similar before (but a bit more complex, involving a failed autogrow and a cancelled tran log backup). In that case a restart of the instance was required, restart recovery took a couple minutes which was far less than we expected.
If you can, take a full backup and then restart the instance (in a maint window preferably)
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply