October 29, 2009 at 3:05 am
Hi DBA's,
We have the auto created table counterdata in a monitoring DB by writing perfmon data to sql server from more than 50 different servers for reporting. The table is filled with more than 20 million rows.
Please provide your suggestions for moving data between 2 days and 30 days to a archival table with same structure and deleting data older than 30 days without any down time if possible.
Table counterdata has no partition. Any chance of creating a new table with same structure & table-partition and using the switch option. Its sql 2005:-)
I am going to hit the bed now as its late night and check for any suggestions in the morning. Thank you.
...Kumar
October 29, 2009 at 4:40 am
For the delete, I'd put the process in a loop deleting, say, 4500 rows at a time until your target removal records are all gone.
The reason for this is to avoid lock escalation, where if you have a process acting on more than c. 5000 (2005 - 3000 on 2000) records rather than use individual row locks hte engine will likely escalate it to a table lock for the duration of the action. Which can be rather unfortunate on a live system
November 2, 2009 at 8:36 pm
November 3, 2009 at 12:53 pm
Partitioning the existing table may be a good way forward for you.
Something like:
Create a 3 partition table to hold data newer than 2 days, between 2 and 30 days and older than 30 days.
Cascade the date between partitions once a day
Delete old data from the 3rd partition (which you can do directly) either by a staggered delete or maybe a truncate if you keep the partition lightly populated.
Something for a test rig me thinks!
Hope this helps.
November 3, 2009 at 1:40 pm
I agree, partitioning seems to be the least resource consuming approach. I would though create 31 partitions for sliding window. Then I would set up a job to move the window one day forward every midnight.
This approach works well for our reporting database. I wrote procedures that manage 11 tables in that manner.
Regards
Piotr
...and your only reply is slàinte mhath
November 3, 2009 at 2:57 pm
If you are looking at table partitions I have to ask, are you running SQL Server 2005 Enterprise Edition or Standard Edition?
Partitioned tables and indexes are only supported in the Enterprise and Developer Editions.
November 3, 2009 at 3:02 pm
Good point, I forgot about this 🙂
If it is not an Enterprise edition, perhaps partitioned view could be also an option.
Piotr
...and your only reply is slàinte mhath
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply