December 20, 2010 at 10:34 pm
Comments posted to this topic are about the item Automate Sliding Window Partition Maintenance: Part II
December 21, 2010 at 9:28 am
Thanks for continuing the series on this subject.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 21, 2010 at 9:36 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply