SQL SERVER – Time Out Due to Executing DELETE on Large RecordSet

Just a day ago, I received following question:

“I have large table more than 1M rows. I want to delete every row in my table. Everytime I ran DELETE statement, it times out and does not do it job. The data in table is useless and I do not need it ever. Your suggestion please.”

The reason I decided to write article about this question because I receive similar questions very often. I think many readers will find answer to this question useful. My answer to his question is here with:
“If DELETE is timing out use TRUNCATE instead. It will be faster and will not be logged in log file.”

However, I recommended few additional points to keep in mind while using TRUNCATE.

Command TRUNCATE will reset identity to initial identity. If you want to keep your identity same you will have to get the maximum identity before you truncate and reset it to that maximum identity. SQL SERVER – DBCC RESEED Table Identity Value – Reset Table Identity . If you are not using identity or do not have use for identity you do not have to worry.

TRUNCATE command if not used with BEGIN and END transaction, you will not be able to roll back using log files. Make sure you absolutely do not need the truncated data. SQL SERVER – TRUNCATE Can’t be Rolled Back Using Log Files After Transaction Session Is Closed

Reference : Pinal Dave (http://blog.SQLAuthority.com)

5 thoughts on “SQL SERVER – Time Out Due to Executing DELETE on Large RecordSet

  1. Also TRUNCATE is not so useful when you are deleting “conditionally”
    e.g. delete 1M rows, in a table with 3M rows

    In this case
    – watch for index buildup (maybe drop-and-create index after delete)
    – watch for log buildup (maybe switch to SIMPLE, then back to FULL recovery)
    – do it in smaller batches

    Like

  2. Hi Pinal,

    When trying to delete 1 M records in a single stretch, Will try to write to the logfile for deletion of all 1 Million records., and then the actual deletion starts. if you try, deleting the records in smaller batch of say 500 records per time, May speed up the process.

    This is my opinion. Please correct me if i am wrong.

    Regards
    Chella Ganesh
    C/o Excel Soft Technologies P Ltd.
    Mysore
    India

    Like

  3. Hi Pinal,

    When i am tryign to use the dbcc

    DBCC CHECKIDENT (Schools.tblCaste,reseed,1)

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘.’.

    Like

  4. >> Also TRUNCATE is not so useful when you are deleting “conditionally”

    I do it as a while loop — something like this

    while (select count(1) from mytable where indexnum 0
    begin
    delete top (5000) from mytable where indexnum < 100000
    end

    That loops through and takes out in chunks. It will grow your tran log, but it shouldn't be as bad, and you don't face a time out issue.

    Like

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #012 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s