RE: partitioned table maintenance

From: Mercadante, Thomas F (LABOR) <>
Date: Wed, 28 May 2008 11:36:05 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF01CD7CB7@EXCNYSM0A1AJ.nysemail.nyenet>


In my mind, you always need a max value partition to catch data errors. The SOP for adding new partitions is to split the MAX partition by the month value and add the new partitions. You can repeatedly do this for the entire year if you want to - setting up all of the months for the year in one DDL script.  

At the same time, drop the oldest partitions that you don't need anymore.  

Hope this helps.  


[] On Behalf Of Joe Smith Sent: Wednesday, May 28, 2008 11:27 AM
Subject: partitioned table maintenance

I have inherited a partitioned table with a MAX VALUE partition. All values more than April 1st are going into that partition.
What is the SOP for adding partitions for May, June, and so on and dealing with the MAX VALUE partition?  

Table                          Partition                 Partition
Name                           Name                       Position
------------------------------ ------------------------- ---------
---------- ----------- ------------------- --------------------
T_TABLE_CALL                    TABLE_200712                      1
0           0 04/24/2008 00:09:27 TABLE_1207_M016
T_TABLE_CALL                    TABLE_200801                      2
0           0 04/24/2008 00:09:28 TABLE_0108_M016
T_TABLE_CALL                    TABLE_200802                      3
0           0 04/24/2008 00:09:28 TABLE_0208_M016
T_TABLE_CALL                    TABLE_200803                      4
0           0 04/24/2008 00:09:28 TABLE_0308_M016
T_TABLE_CALL                    TABLE_MAX                         5
74710         119 05/23/2008 22:00:47 TABLE_MAX_M016


E-mail for the greater good. Join the i'm Initiative from Microsoft. < GreaterGood>

Received on Wed May 28 2008 - 10:36:05 CDT

Original text of this message