Search code examples
sql-serversql-server-2008-r2sql-delete

Deletion of table takes too long

I am trying to delete a big table (11 GB size).

The table is so big because of a bug in the application who writes the table. The table is a kind of custom made db change log in which by mistake updates to blob fields are logged too (causing the DATALENGTH(OneSpecificField) to be over 140MB in some cases for a single record, while the expected max size should have been 2 kB).

I tried to delete the table by running

DELETE CUSTOM_LOG
GO

The execution takes more than 10 minutes, being a production DB I do not like to wait forever (even if the users do not experience significant problems, since logging is now disabled).

If no one is accessing the table why it should take so long?

Solution

  • If the table is not referenced by a FK, you can use TRUNCATE TABLE CUSTOM_LOG, it should work much faster