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 SmithAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nemws1Database AdministratorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.