How to kill Rollback process

Question

Post reply

How to kill Rollback process

balbirsinghsodhi

SSCrazy Eights

Points: 9210
More actions
March 26, 2008 at 5:49 pm

#121182

Is there any way to kill rollback process. It's running for hours.

Viewing 15 posts - 1 through 15 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Vivien Xing SSChampion Points: 12297 More actions · Answer 1

What was the process doing? How long has the process been running before the kill command?

Does sp_who2 show the IO changes?

You can use KILL spid WITH STATUSONLY to see the rollback progress.

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 2

IO is increasing and it's running 5 hours.

Actually, I was in a process of deleting records and then cancel the query.

I think even if I want to restart the sql server service, it will still be there.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

You cannot - if you were able to kill a rollback, it would leave your database in an inconsistent state. If you stop and start the SQL service, it will continue the rollback when it starts.

You must simply wait for it to finish.

Vivien Xing SSChampion Points: 12297 More actions · Answer 4

how long the deletion had been running before "kill"? It most likely takes the same amount of time to roll back.

Huge amount of data to delete within 1 transaction? Sigh...

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 5

Thanks guys,

Recovery process is finished and it took few hours. I have noticed one more thing that even if Database is in simple recovery mode , Transaction log files still grows.

Don't you guys think that if Database is in simple recovery mode then it should not fill transaction log files.

I thought that if you are importing large chunk of data and if database is in simple recovery mode than it should use transaction log but it's not the case.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 6

Simple recovery does not mean the log is not used. If transaction were not logged in the t-log how would you rollback transactions? This is why the t-log still gets full in the SIMPLE recovery model. The t-log is truncated at every checkpoint.

My blog: http://jahaines.blogspot.com

Vivien Xing SSChampion Points: 12297 More actions · Answer 7

Right. As it is called "Transaction" log. One Delete statement is one transaction no matter how may records or simple model.

For import data like Bulk Insert, there is an option BATCHSIZE, take a look BOL for details.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 8

Michael Earl (3/27/2008)
You cannot - if you were able to kill a rollback, it would leave your database in an inconsistent state. If you stop and start the SQL service, it will continue the rollback when it starts.
You must simply wait for it to finish.

sometimes we get a rollback get hung up and the only way to kill it is to restart SQL or reboot. never had an inconsistent db in this case

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

SQL Noob (3/27/2008)
Michael Earl (3/27/2008)
You cannot - if you were able to kill a rollback, it would leave your database in an inconsistent state. If you stop and start the SQL service, it will continue the rollback when it starts.
You must simply wait for it to finish.
sometimes we get a rollback get hung up and the only way to kill it is to restart SQL or reboot. never had an inconsistent db in this case

No, because SQL finished the rollback after the server restarts. What Michael's saying is if there was a way to cancel a rollback (which there isn't) you could get a transactionally inconsistent DB.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 10

Adam Haines (3/27/2008)
Simple recovery does not mean the log is not used. If transaction were not logged in the t-log how would you rollback transactions? This is why the t-log still gets full in the SIMPLE recovery model. The t-log is truncated at every checkpoint.

I recall reading somewhere that in some situations, during an extraordinarily long transaction, checkpoints are missed (not issued), thereby causing the bloating of the txn log even in simple recovery.

Comments on this anyone?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Dunno about missing checkpoint, but if there's an open transaction then the log records can't be discarded until the transaction has completed.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ghanta Hall of Fame Points: 3999 More actions · Answer 12

Say if we run updates in batches

and if we use

set xact_abort on -- rollback and bail on error

Any due to some reason if we have to kill the update, will this try to rollback all the batches or just the last batch?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Depends.

If you're doing something like this, all will be rolled back

Begin transaction

set xact_abort on

update ...

update ....

commit transaction

If each update is within its own transaction (or if you're not explicitly setting transactions at all, then just the one that threw an error will be rolled back.

A rollback will undo everything in the current transaction.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

allan_leake SSC Veteran Points: 280 More actions · Answer 14

We had a situation where a scheduled job invokes a stored procedure that will refresh an SQL Server table from a corresponding DB2 table on an i5 (or AS400 if you are more familiar with that term). For some reason the job went into a OLEDB wait state and got stuck there for many hours. Using the GUI I killed the process. This promptly went into "KILL/ROLLBACK" in the OLEDB wait state and sat there for hours.

Eventually we killed the SQL Server Service (actually the resource because it was on a cluster). That did the trick.

The SQL table was intact after we did this - probably because no action had occurred on it due to the fact that the communication to the i5 box was the issue (i.e. there were no transactions to roll back in the first place).