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 |
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
 Posted - 2011-10-25 : 13:37:44
|
| I have created a transcationhistory table of 5 partitions when the load job runs everyday which would store 1 day of data in each table.I want to remove the oldest data from transcationhistory table keeping 5 days of data in the table.Partition1:Day1dataPartition2:Day2dataPartition3:Day3dataPartition4:Day4dataPartition5:Day5dataHere are steps to perform.1)At start of each day,the oldest data of transcationhistory partition table should be switch to the transactionhistoryarchive table.Ex:In above sceniario, the oldest data in the transcationhistory is partition1 table. 2)Truncate the transactionhistoryarchive table.This process needs to be done daily to load new data and remove the oldest data.How do i acheive this.let me know if it is not clear.. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-10-25 : 18:15:48
|
| Are you using Enterprise Edition? If so, this is a good opportunity to use partitioning: http://goo.gl/tif6Q |
 |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-10-26 : 02:41:33
|
| When a new days starts you will have to switch the oldest partition to the archive table.Then remove the oldest range from the partition function and then alter the partition scheme to next used file in the filegroup.Then add a new partition limit to the partition function (which should be date in your case) and move the new days data to the main table.For this you will have to create a staging table in which you can load your new days data.PBUH |
|
|
|
|
|
|
Data movements