[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2013-11-12
4
Medium Priority
?
1,076 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 43

Assisted Solution

by:pcelba
pcelba earned 100 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 1000 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 1000 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 100 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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 …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

656 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