Solved

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

Posted on 2013-11-12
4
980 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 41

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now