SQL Server Timeout Deleting Records

Posted on 2014-08-03
Last Modified: 2014-08-03
I am trying to Delete Records in a Table, but keep getting timeout errors, and am having to select 50 000 at a time.
I have got about 4  million records in the table, and would purely like to empty the table
Question by:JacquesSmith
    LVL 13

    Accepted Solution

    If you want the table cleared of ALL data quickly, use TRUNCATE instead of DELETE.

    Open in new window

    LVL 23

    Assisted Solution

    I assume you can't just call 'TRUNCATE TABLE BigTable;' for some reason (is it being replicated?)  That would be quickest/fastest way.

    Otherwise, I'm guessing most people would think CTE or use a cursor to solve this, but you might run into the same problem with those.  What you really need to do use separate batches (multiple 'GO' statements) so that any tables locks are released while you are deleting records.

    Have you thought about doing a little dynamic SQL to script out the changes?  I'm going to assume you have an IDENTITY column (INT) of some sort.  Run the following SQL (changing "BigTable" and "IdintityField" appropriately) in SSMS with TEXT output mode turned on.  Then copy/paste the output into a new window and run it.

    DECLARE @total_count INT;
    DECLARE @chunk_size INT = 50000;
    select @total_count = MAX(IdentityField) from BigTable;
    DECLARE @chunk_start INT = @chunk_size;
    WHILE (@chunk_start <= @total_count)
    	PRINT 'DELETE FROM BigTable WHERE IdentityField < ' + CAST(@chunk_start AS VARCHAR) + ';'
    	PRINT 'GO';
    	SET @chunk_start = @chunk_start + @chunk_size;

    Open in new window

    Should produce a bunch of statements like:
    DELETE FROM BigTable WHERE IdentityField < 50000;
    DELETE FROM BigTable WHERE IdentityField < 100000;
    DELETE FROM BigTable WHERE IdentityField < 150000;
    DELETE FROM BigTable WHERE IdentityField < 200000;

    Open in new window

    LVL 68

    Expert Comment

    If you don't have any foreign keys referencing the table, and you don't need any DELETE trigger(s) to fire, you could use TRUNCATE TABLE.

    But a loop of 50K at a time for 4M rows wouldn't take that long either, and in SIMPLE recovery mode would require much less current log space.

    Author Closing Comment

    Thanks, they both work well

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now