SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Partitioning
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shaggy
Posting Yak Master

India
245 Posts

Posted - 02/23/2013 :  08:20:56  Show Profile  Reply with Quote
Hi,
Can anyone explain what is the effective way to do data purging using sql server Partitioning.

if possible kindly provide any links

Thanks.

James K
Flowing Fount of Yak Knowledge

3562 Posts

Posted - 02/23/2013 :  09:33:06  Show Profile  Reply with Quote
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?
Go to Top of Page

shaggy
Posting Yak Master

India
245 Posts

Posted - 02/25/2013 :  03:48:33  Show Profile  Reply with Quote
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.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/25/2013 :  04:16:46  Show Profile  Reply with Quote
why cant you create a view over your table and warehouse table so as select one year worth of data combining them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

shaggy
Posting Yak Master

India
245 Posts

Posted - 02/25/2013 :  06:46:38  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000