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 |
|
shaggy
Posting Yak Master
248 Posts |
 Posted - 2013-02-23 : 08:20:56
|
| Hi,Can anyone explain what is the effective way to do data purging using sql server Partitioning.if possible kindly provide any linksThanks. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-23 : 09:33:06
|
| Usually partitioning is not used as part of purging. If you are planning to purge some of the data, there is really no need to partition that - that data will be gone anyway after you purge. Can you explain in a bit more detail what your goal is? |
 |
|
|
shaggy
Posting Yak Master
248 Posts |
Posted - 2013-02-25 : 03:48:33
|
| Thanks James,The task is to Archive of old records for Eg:- My audit transaction report will be executed month wise today if i run i will get set of data of this month only if i run on march 2 i will get data only for 2 days so my partition is month wise every year end my data will be moved to warehouse table by insert and truncate to current live table recently i came across switch partition so i thought of doing switch partition to staging table and moving staging data to warehouse table every month instead of holding a year data. can anyone tell me whether this approach is good to proceed. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-25 : 04:16:46
|
| why cant you create a view over your table and warehouse table so as select one year worth of data combining them?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
shaggy
Posting Yak Master
248 Posts |
Posted - 2013-02-25 : 06:46:38
|
| visakh,Iam not worried about fetching data because my application will get data only for a current month.currenly my purging rule for my live table is for a year eg:for cuurent year my purging activity will happen on some day on dec 2013 till that time my data will be available in the table as it is a old system we are plaaning for some changes we have concluded this purging activity is taking very long time to process because it is moving data for a year so we thought of moving data every month so we can break some work. my approach is every previous month end i will do switch partition(takes seconds to proceed but doing schema level lock) to a staging table and again i will move it to warehouse table. Note:my table is monthwise partition.so this approach will not reduce IO for application (my fetch will be current month wise) but it saves time for moving data to warehouse table. |
|
|
|
|
|
|
Partitioning