Alter Partition Function with existing data problem

• dlauth

  Valued Member

  Points: 54

  More actions

  October 3, 2013 at 8:59 am

  #298580

  I have inherited a datamart on SQL 2008 R2 that has an existing partition function in place for our Fact data broken up by months. It was setup to go out as far as the end up 2012, but was never extended out past that. This wasn't discovered until about a month ago and by then the last file group (12/20012) had the data for 1/2013 through the current day. My previous attempts to resolve this using the split range option has resulted in a some pretty large tran logs which ultimately ended up in me running out of disk space. I'd be okay with leaving the large partition in place that contained a data for 12/2012-10/2013 if I knew I'd be able to roll 11/2013, 12/2013, etc in their correct partitions going foward.

  My partition schema/functions look like this:

  CREATE PARTITION SCHEME [ds_partition_fact_scheme_month] AS PARTITION [ds_partition_fact_fn] TO ([fact_2003_1_partition_filegroup…..[fact_2012_12_partition_filegroup]]

  CREATE PARTITION FUNCTION [ds_partition_fact_fn](int) AS RANGE LEFT FOR VALUES (1127, 1155……4718) (where each range value represents the first day of every month in our time dimension table and 4718 is 12/1/2012)

  I've added all the files/filegroups going out for 12 months in the future and tried running the following commands:

  ALTER PARTITION SCHEME ds_partition_fact_scheme_month NEXT USED [fact_2013_11_partition_filegroup]

  and

  ALTER PARTITION FUNCTION ds_partition_fact_fn()

  SPLIT RANGE (5054) (where 5054 represents 11/1/2013 in our time dimension table)

  When I run the alter partition function command, my tran log grows until I run out of disk space once again (over 200 GB in tran log) despite the fact that I haven't accumulated any data for Nov 2013 yet. I wouldn't have thought there should not have been any data to be moved. Maybe I'm misunderstanding what's going on.

  Based on what I'm trying to accomplish, am I doing this right? I seem to be missing something.

  Thanks

  Dave