Solved

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

Posted on 2013-11-12
4
1,036 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 25

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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

695 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