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.
| Author |
Topic |
|
rocksteer
Yak Posting Veteran
69 Posts |
 Posted - 2011-09-22 : 09:46:40
|
| Hi,I am using SQL Server 2000I 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 iterationPause 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. |
 |
|
|
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. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-22 : 11:07:54
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=163778 |
|
|
|
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? |
|
|
|
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? |
|
|
|
|
|
|
|
|
Deleting old data from large tables