Cancel a SQL Server Query

November 29, 2007 12:53 PM

Please help me understand what happens when I click the cancel query button in SQL Server Management Studio.

Please help me understand what happens when I click the cancel query button in SQL Server Management Studio.

I was running a delete query in SQL Server 2005 on a pretty slow machine with low resources on a pretty big database table. After about 2.5 hours, I got a message that I was running low on disk space and when I checked, drive C had 5MB free out of 75GB.
Anyway, I went to cancel the query and here I sit, one hour later and it still says "Canceling Query". Am I going to have to wait another hour and a half for this? Will any of the deletes have been committed or did I just waste 3 and a half hours of time?

posted by smithygreg to Computers & Internet (3 answers total)

Best answer: The deletes won't be committed. You're running out of disk space because it has to log all of those deletes so they can be rolled back if the statement terminates (which is what it's doing while you wait for it to cancel). If you want to delete all records in the table, try using TRUNCATE instead (which isn't logged). If you need to delete only part of the records, try rewriting your query to delete a smaller number of records at a time (say 1000), and then run the query repeatedly until everything you want is gone. You might also try dropping any indexes on the table, and dropping any foreign keys from other tables TO your table, while deleting, and then re-create them afterwards. It may run much quicker that way.
posted by Emanuel at 1:31 PM on November 29, 2007

Also here's some info to get your disk space back: http://support.microsoft.com/?kbid=873235
posted by Emanuel at 1:34 PM on November 29, 2007

Response by poster: Thanks Emanuel...
I wound up just dropping the table altogether and recreating it.
posted by smithygreg at 2:14 PM on November 29, 2007