Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Deleting old data from large tables

Author  Topic 

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2011-09-22 : 09:46:40
Hi,
I am using SQL Server 2000

I have some large tables that I load daily.
I have about 3 years worth of data, but only want about 1 1/2 years.
The tables are quite heavily indexed, as they are used for reporting.

It takes about 10 minutes to delete a single day's data. I have been deleting about 5 days at a time to allow the changes to commit and be able to monitor the results.

The database is in simple recovery mode.

I cannot truncate, and the database is to large to just copy, truncate, and replace.

Is there a better way?

Kristen
Test

22859 Posts

Posted - 2011-09-22 : 10:10:18
"Is there a better way?"

Dunno if it is better! but for tables where we have a lot of data to delete I:

Put all the PKs to be deleted into a #Temp table (with an ID IDENTITY column)

In a loop I delete by joining the #Temp to the main table, using the ID column to get a "reasonable" range for deletion - say 10,000 rows each iteration

Pause using WAITFOR (4 or 5 seconds-ish) after each loop iteration to give other processes time to recover.

This means that the DELETE Batch, per iteration, is modest - so it doesn't cause the LDF file to be extended (on FULL Recovery model you would also need to increase the frequency of Tlog backups)

The main reason for using a #Temp table is that the Query to find the old stale data takes quite a significant time, so deleting a block of records, in a loop, using a WHERE clause to find them is slower than just joining the PKey from #Temp to Main Table.
Go to Top of Page

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2011-09-22 : 10:56:50
Thanks Kristen,

You have some code that does this. I am not that up on stored procedures.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2011-09-22 : 11:07:54
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=163778
Go to Top of Page

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2011-10-05 : 11:00:51
Our Installation folks are thinking of using XP Professional 2002, svc pack 3 as the platform to install SQL Server 2000. Is this an acceptable platform?
Go to Top of Page

rocksteer
Yak Posting Veteran

69 Posts

Posted - 2011-10-05 : 11:13:13
Let me modify my question. What are acceptable platforms for SQL Server 2000?
Go to Top of Page
   

- Advertisement -