Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Automate Sliding Window Partition Maintenance: Part II Expand / Collapse
Author
Message
Posted Monday, December 20, 2010 10:34 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 3, 2014 7:35 PM
Points: 401, Visits: 166
Comments posted to this topic are about the item Automate Sliding Window Partition Maintenance: Part II


Post #1037499
Posted Tuesday, December 21, 2010 9:28 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 3:08 PM
Points: 17,621, Visits: 15,476
Thanks for continuing the series on this subject.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1037807
Posted Tuesday, December 21, 2010 9:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 1, 2014 10:53 AM
Points: 14, Visits: 265
I have encouraged our DBA team to get powershell installed (SQL 2005) - no luck yet. Meanwhile I use a procedure for each partitioning function. Here is an example where boundaries are YYYYMM. For monthly loads - we call this in ETL process before insert. You'll notice no use of filegroups - it would take a bit more code if we were strategically using filegroups.

Ommitted try/catch, error handling to keep is short.

create procedure [dbo].[CreateYearMonthPartition] @YearMonth int as
declare @PartitionId int
-- check if partition for this period already exists
select @PartitionId = rv.boundary_id + 1
from sys.partition_functions pf
join sys.partition_range_values rv on pf.function_id=rv.function_id
where pf.name='YearMonth_RangeR'
and rv.value=@YearMonth
if @PartitionId is null
begin
-- partition does not exist; create it.
alter partition scheme [YearMonth_RangeR_scheme]
next used [PRIMARY]
alter partition function [YearMonth_RangeR]()
split range (@YearMonth)
end


Post #1037812
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse