Solved

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

Posted on 2013-11-12
4
1,016 Views
Last Modified: 2013-11-25
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
0
Comment
Question by:PKTG
  • 2
4 Comments
 
LVL 42

Assisted Solution

by:pcelba
pcelba earned 25 total points
ID: 39643174
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...
0
 
LVL 13

Accepted Solution

by:
magarity earned 250 total points
ID: 39643233
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
begin
delete from table where primarykey in (select top 10000 primary key from table where datefield < mydate);
set @deletemore = @@ROWCOUNT;
end
0
 
LVL 13

Assisted Solution

by:magarity
magarity earned 250 total points
ID: 39643330
PS - If this is a busy transactional system you might consider the ROWLOCK hint when doing large amounts of deletes.
0
 
LVL 24

Assisted Solution

by:chaau
chaau earned 25 total points
ID: 39643361
Also, to reduce the database locks during business hours, you can create a SQL Agent job and schedule the deletion to after hours
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard 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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 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