[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 619
  • Last Modified:

SQL Server Timeout Deleting Records

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
Jacques Smith
Jacques Smith
2 Solutions
Russell FoxDatabase DeveloperCommented:
If you want the table cleared of ALL data quickly, use TRUNCATE instead of DELETE.

Open in new window

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) + ';'
	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

Scott PletcherSenior DBACommented:
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.
Jacques SmithAuthor Commented:
Thanks, they both work well

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now