Automate Sliding Window Partition Maintenance: Part II

• Hugh Scott

  SSC Eights!

  Points: 987

  More actions

  December 20, 2010 at 10:34 pm

  #229719

  Comments posted to this topic are about the item Automate Sliding Window Partition Maintenance: Part II

• SQLRNNR

  SSC Guru

  Points: 281344

  More actions

  December 21, 2010 at 9:28 am

  #1265150

  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

• Idea Deadbeat

  SSC-Addicted

  Points: 416

  More actions

  December 21, 2010 at 9:36 am

  #1265153

  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