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

How to delete large number of records from SQL Server 2008 R2 Table?

I need to delete older than year 2009 records from table and keep only year 2010 and above records in the table. It is almost 1 million records to delete.

I thought of deleting records using below script but it will create transaction log in the database. So please let me know the best method to delete records from the table.

Delete from tablename where reportdate <= 20090101
  • 2
4 Solutions
Depends how many records will remain in the table. If we are talking let say about 100.000 records then it is better to create a new table with these records and then truncate the original table and insert records from previously created table...

BTW, what is the problem with log creation?  You may simply delete records and then truncate the log...
If you're worried about spooling out such a large transaction in one go, you can use a loop to delete them in batches:

declare @deletemore int
set @deletemore = 1
while @deletemore > 0
delete from table where primarykey in (select top 10000 primary key from table where datefield < mydate);
set @deletemore = @@ROWCOUNT;
PS - If this is a busy transactional system you might consider the ROWLOCK hint when doing large amounts of deletes.
Also, to reduce the database locks during business hours, you can create a SQL Agent job and schedule the deletion to after hours
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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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