| Author |
Topic |
|
sean_micken
Starting Member
4 Posts |
 Posted - 2008-01-29 : 11:05:37
|
| Guys,First off, I'm not very familiar with SQL Server. I need some guidance on what the best path to take is for this as it may not even be table partitions.I have a huge table (155 million rows) and it's gotten so large than I can't even delete a large set of rows from it (i.e. delete everything older than 6 mo, which would be ~100 million rows). When trying to run a delete like this, it just goes for a LONG time and then just eventually runs out of memory.The current data in this table can actually be completely cleared out soon (after Feb 1st) and I plan to do this with TRUNCATE TABLE, or just DROP and recreate. Once I do this, I want to create a way to keep this table moderately sized so it never grows that large again and it seems table partitions may be the way to go for this?I'd like to keep the last 6mo of data in it (I have a datetime column to keep track of this). Anything older I'd like automatically removed. Can I do this with table partitioning? Create 6 partitions that store the 6 most recent months of data and everything older automatically gets dropped off?If not partitions, what do you suggest to keep this DB modest size?Thank you. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-29 : 23:03:18
|
| You have to delete old rows in smaller batch, like 10 days data for instance. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-01-29 : 23:23:14
|
| Use a loop to delete the data in small chunks, like 50,000 to 100,000 rows at a time.If you delete a large number of rows at one time, you should consider either reindexing the table or defragmenting the indexes on the table followed by updating the statistics as soon as you are done with the purge. CODO ERGO SUM |
|
|
|
sean_micken
Starting Member
4 Posts |
Posted - 2008-01-31 : 11:19:05
|
| Thanks for the replies.We have decided to just delete all the rows from the table. I guess this means using TRUNCTATE TABLE.Michael Valentine Jones,How would I go about reindexing or defragging indexes once we TRUNCTATE this table? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-01-31 : 11:33:52
|
| If you are going to TRUNCTATE the table, there is really no need to reindex or defrag since there is no data to work with.CODO ERGO SUM |
|
|
|
sean_micken
Starting Member
4 Posts |
Posted - 2008-01-31 : 13:50:36
|
| Okay. I've read that when you truncate a table, the data is just "deallocated" but its still there. Does this mean my DB size will be the same on disk? If so, how can I quickly get rid of that data? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-01-31 : 14:32:11
|
| Definitely table partition will be the best option for you. It is supported only in Enterprise edition of SQL server 2005. Yes you can definitely switch out the unwanted partition and performance will be real faster because you table will divided into different partition and spread across multiple disk subsystems. |
|
|
|
sean_micken
Starting Member
4 Posts |
Posted - 2008-01-31 : 14:39:28
|
quote:
Originally posted by sodeep
Definitely table partition will be the best option for you. It is supported only in Enterprise edition of SQL server 2005. Yes you can definitely switch out the unwanted partition and performance will be real faster because you table will divided into different partition and spread across multiple disk subsystems.
Will I have to switch the partitions out by hand or can it handle that automatically? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-01-31 : 14:41:06
|
| you will have to do manually. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-31 : 23:36:15
|
| You can write script for that and run it as sql job. |
|
|
|
|
Keeping a table moderatley sized (partitions?)