Partition Buckets not getting updated on a Partition table

• vamshikris422

  Grasshopper

  Points: 23

  More actions

  August 23, 2012 at 9:17 am

  #280147

  Hi,

  I have a SQL Server 2008 table which is partitioned into 28 weekly buckets based on a field timestamp.

  Reason being, the table contains humongous data and we archive data that is older than 6 months.

  But for some reason the partition function is stuck at the range november 2011 to may 2012, where it should have been 6 months back, to the current date.

  Is there any way we can update the partition function to point to the right ranges?

  Any help would be much appreciated as I am in an urgent need of this.

  Thanks!

• Jared

  SSC Guru

  Points: 61793

  More actions

  August 23, 2012 at 12:56 pm

  #1529422

  vamshikris422 (8/23/2012)

  ---

  Hi,

  I have a SQL Server 2008 table which is partitioned into 28 weekly buckets based on a field timestamp.

  Reason being, the table contains humongous data and we archive data that is older than 6 months.

  But for some reason the partition function is stuck at the range november 2011 to may 2012, where it should have been 6 months back, to the current date.

  Is there any way we can update the partition function to point to the right ranges?

  Any help would be much appreciated as I am in an urgent need of this.

  Thanks!

  What do you mean the function is "stuck?" Do you mean that the function was created incorrectly? Can you post the DDL for the function, schema, and table?

  Jared
  CE - Microsoft

• vamshikris422

  Grasshopper

  Points: 23

  More actions

  August 23, 2012 at 1:33 pm

  #1529441

  Thanks for the reply Jared!

  I meant that the function is declared with 28 buckets of time each worth a week. We need that to be a rolling window which would move forward every tuesday according to our requirement. But that doesn't happen now.

  The DML for that function is,

  CREATE PARTITION FUNCTION [prf_fct_time_by_week](datetime) AS RANGE RIGHT FOR VALUES (N'2011-11-20T00:00:00.000', N'2011-11-27T00:00:00.000', N'2011-12-04T00:00:00.000', N'2011-12-11T00:00:00.000', N'2011-12-18T00:00:00.000', N'2011-12-25T00:00:00.000', N'2012-01-01T00:00:00.000', N'2012-01-08T00:00:00.000', N'2012-01-15T00:00:00.000', N'2012-01-22T00:00:00.000', N'2012-01-29T00:00:00.000', N'2012-02-05T00:00:00.000', N'2012-02-12T00:00:00.000', N'2012-02-19T00:00:00.000', N'2012-02-26T00:00:00.000', N'2012-03-04T00:00:00.000', N'2012-03-11T00:00:00.000', N'2012-03-18T00:00:00.000', N'2012-03-25T00:00:00.000', N'2012-04-01T00:00:00.000', N'2012-04-08T00:00:00.000', N'2012-04-15T00:00:00.000', N'2012-04-22T00:00:00.000', N'2012-04-29T00:00:00.000', N'2012-05-06T00:00:00.000', N'2012-05-13T00:00:00.000', N'2012-05-20T00:00:00.000')

  GO

  The table is built using this partition function and has a field named "time_stamp" that is used for putting the data into different partitions.

  Thanks,

  Vamshi.

• Jared

  SSC Guru

  Points: 61793

  More actions

  August 23, 2012 at 1:42 pm

  #1529443

  vamshikris422 (8/23/2012)

  ---

  Thanks for the reply Jared!

  I meant that the function is declared with 28 buckets of time each worth a week. We need that to be a rolling window which would move forward every tuesday according to our requirement. But that doesn't happen now.

  The DML for that function is,

  CREATE PARTITION FUNCTION [prf_fct_time_by_week](datetime) AS RANGE RIGHT FOR VALUES (N'2011-11-20T00:00:00.000', N'2011-11-27T00:00:00.000', N'2011-12-04T00:00:00.000', N'2011-12-11T00:00:00.000', N'2011-12-18T00:00:00.000', N'2011-12-25T00:00:00.000', N'2012-01-01T00:00:00.000', N'2012-01-08T00:00:00.000', N'2012-01-15T00:00:00.000', N'2012-01-22T00:00:00.000', N'2012-01-29T00:00:00.000', N'2012-02-05T00:00:00.000', N'2012-02-12T00:00:00.000', N'2012-02-19T00:00:00.000', N'2012-02-26T00:00:00.000', N'2012-03-04T00:00:00.000', N'2012-03-11T00:00:00.000', N'2012-03-18T00:00:00.000', N'2012-03-25T00:00:00.000', N'2012-04-01T00:00:00.000', N'2012-04-08T00:00:00.000', N'2012-04-15T00:00:00.000', N'2012-04-22T00:00:00.000', N'2012-04-29T00:00:00.000', N'2012-05-06T00:00:00.000', N'2012-05-13T00:00:00.000', N'2012-05-20T00:00:00.000')

  GO

  The table is built using this partition function and has a field named "time_stamp" that is used for putting the data into different partitions.

  Thanks,

  Vamshi.

  Ok, you are talking about a "sliding window" scenario. Google that and it should help you out. Here's 1 article http://msdn.microsoft.com/en-us/library/aa964122%28v=sql.90%29.aspx

  Jared
  CE - Microsoft