March 16, 2009 at 9:38 pm
I need to remove old data from 2 tables in a database of ours. The problem is I'm new to SQL and the tables have to be accessible at all times.
On other, smaller tables in other databases, we've exported the data we want, truncated the table and then imported the data we need back in. Before we started, we stopped the jobs that were adding data to the rows and when finished, let it continue.It's a bit slow but it worked.
Unfortunately the main table I want to reduce is about 160 million rows and is live. We can't stop the jobs that have info coming in for more than about an hour.
Any suggestions?
March 16, 2009 at 11:33 pm
Can you provide more information about the tables? The DDL (CREATE TABLE statments) and the indexes defined would be beneficial.
The key idea is to identify what records you want to delete and then delete them in batches to prevent the table from being locked.
Oh, just to verify, you are using SQL Server 2000, correct? I don't want to accidently give you a SQL Server 2005 solution even though this is posted in a SQL Server 7, 2000 forum.
March 16, 2009 at 11:59 pm
Lynn Pettis (3/16/2009)
Can you provide more information about the tables? The DDL (CREATE TABLE statments) and the indexes defined would be beneficial.The key idea is to identify what records you want to delete and then delete them in batches to prevent the table from being locked.
Oh, just to verify, you are using SQL Server 2000, correct? I don't want to accidently give you a SQL Server 2005 solution even though this is posted in a SQL Server 7, 2000 forum.
I'm afraid I really am a novice so please bear with me. These databases were created a good few years before I (recently) started. It's definetly SQL Server 2000 though.
The table is not very complex, basically just 7 or 8 fields that are passed in from another server. The index seems to be Timestamp, which is a datetime field.
The problem is that data is added at about 175,000 rows a day and I can't really stop that. I want to delete all the rows prior to this year (it goes back to early 2007), and shrink the database a bit.
March 17, 2009 at 10:33 am
The Idea here is if you delete data in small enough batches it will not lock the table.
If necessary, select out the data into an archive table.
Then for example delete the data one day at a time during Slower periods.
April 14, 2009 at 6:37 pm
Thanks to all, I reduced the databse down to about half the size it was.
No locking and it even shrunk without complaining.
Again, thanks.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply