Automate Sliding Window Partition Maintenance: Part II

  • Hugh Scott

    SSC Eights!

    Points: 987

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

  • SQLRNNR

    SSC Guru

    Points: 281252

    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

    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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply