DELETE running for 8 hours need to Stop the process

Question

Post reply

DELETE running for 8 hours need to Stop the process

Welsh Corgi

SSC Guru

Points: 116520
More actions
November 6, 2013 at 6:08 am

#300809

Someone execute a DELETE query and it has been running for for 8 hours need to Stop the process.
If I kill the SPID it will take at least 8 hours to rollback.
What options do I have?
ROLLBACK IMEDIATE, NO_WAIT?
Any help would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

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

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

Wait for it to finish or stop it and wait for it to rollback. That's pretty much it.

Or drop the database and restore from backups.

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

The Transaction Log Backup is 145 GB. Tried shrinking it but it did not make much of a difference.

They tried canceling the Query from SSMS and closed the window so I can not stop the query.

What about ROLLBACK IMMEDIATE?

If I kill the process it will take at least 9 hours to rollback.

If I restart SQL Server Service that could corrupt the Database.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

Welsh Corgi (11/6/2013)
The Transaction Log Backup is 145 GB. Tried shrinking it but it did not make much of a difference.

No, it won't because there's no free space in the log because of an active transaction (the delete). Shrink removes unused space, if the log is growing then it follows there's no unused space for shrink to remove.

They tried canceling the Query from SSMS and closed the window so I can not stop the query.

You can use the KILL command to cancel the query and start a rollback, though if they tried cancelling from SSMS it it's probably already in rollback.

What about ROLLBACK IMMEDIATE?

From Books Online (ALTER DATABASE)

Specifies when to roll back incomplete transactions when the database is transitioned from one state to another. If the termination clause is omitted, the ALTER DATABASE statement waits indefinitely if there is any lock on the database. Only one termination clause can be specified, and it follows the SET clauses.
ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
Specifies whether to roll back after the specified number of seconds or immediately.

The IMMEDIATELY is when to start any necessary rollback.

If I kill the process it will take at least 9 hours to rollback.

Probably yes, maybe longer.

If I restart SQL Server Service that could corrupt the Database.

No it won't. It will however continue the rollback after the restart, with the database unavailable.

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

I restarted SQL Server Service.

It is at 0% recovered. Not moving.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

Hope no one needs to use that database for the next several hours...

As I said, if you restart, the rollback will continue with the database unavailable. It'll be unavailable until the recovery completes, however long that is.

Or you can drop the database and restore from backups, assuming you have backups that will allow you to restore up to present.

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

Abu Dina SSChampion Points: 14155 More actions · Answer 6

Welsh Corgi (11/6/2013)
I restarted SQL Server Service.
It is at 0% recovered. Not moving.

Let's hope you've got fast disks:-)

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Recovery at 88% Complete.

Keeping my fingers crossed. 🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Recovery completed.

Thanks for the help.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

Next time don't restart SQL while there's a large transaction rolling back.

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

chandan_jha18 SSChampion Points: 11330 More actions · Answer 10

GilaMonster (11/6/2013)
Next time don't restart SQL while there's a large transaction rolling back.

One question here, don't you think the T-log file will keep growing even during the rollback? If the data needs to be put again, won't it add those entries to the log file again and make it grow.

I haven't tested this on my side yet, I think I can initiate a big delete on my machine, kill it and using undocumented functions, will try to read the entries in the log file.

Thanks

Chandan Jha

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

chandan_jha18 (11/7/2013)
GilaMonster (11/6/2013)
Next time don't restart SQL while there's a large transaction rolling back.
One question here, don't you think the T-log file will keep growing even during the rollback?

No. Not from the delete anyway. From other operations maybe.

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

sqlnaive SSCoach Points: 17437 More actions · Answer 12

Interesting read. Gives the pretty much idea on what you can end up into if not consider the impact of the DML.

chandan_jha18 SSChampion Points: 11330 More actions · Answer 13

sqlnaive (11/7/2013)
Interesting read. Gives the pretty much idea on what you can end up into if not consider the impact of the DML.

Burned my hands with heavy deletes once, so always performed this operation in batches thereafter so that even if it had to rollback for whatever reason, it can do it fast enough plus doing it this way controls your CPU else delete operation is just nasty.

Cheers!!

Chandan

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

chandan_jha18 (11/7/2013)
sqlnaive (11/7/2013)
Interesting read. Gives the pretty much idea on what you can end up into if not consider the impact of the DML.
Burned my hands with heavy deletes once, so always performed this operation in batches thereafter so that even if it had to rollback for whatever reason, it can do it fast enough plus doing it this way controls your CPU else delete operation is just nasty.
Cheers!!
Chandan

I did not do the delete but yesterday I told them to run in batches and commit so that tis does not happen.

I have had issues with other DML operations from other users.

I was supposed to be on vacation.

Maybe I will be able to relax today.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/