How to Implement an Automatic Sliding Window in a Partitioned Table on SQL Server 2008

Horizontal table partitioning is first introduced in Sql Server 2005. Table partitioning allows to store data from each period (month, year, etc) to individual partitions (FileGroups). This enables us to perform archiving or purging activities with little data movement. It is a meta data operation, where a partition (of main table) is instantaneously moved to another table (archive table) . At the end of the operation, the data located in the partition is now with  (or moved to) the archive table. So through this meta data operation we can essentially move data from one table to the other in an instant. While there is no actual data movement, it is just a meta data operation where this partition is not assigned to a new table.

There are some limitations to this operation.

• A table could have many partitions and each partition could be assigned to a separate file on dedicated.
• Sliding widow technique allows us to fully automate the whole process to allow growth of data to newer partitions.

Following procedure could be used to automate this sliding-window using a Sql Agent Scheduled job.

-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 -- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 -- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 --
 -- Automate Sliding Window for Partitioning (SPLIT & MERGE)
 --
 -- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 -- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 -- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 CREATE PROCEDURE dbo.sp_split_merge_SampleTable
 AS
 DECLARE @merge_range INT
 , @split_range INT

-- STEP 1:
 -- SWITCH PARTITION 1 (both tables)
 -- so data from Partition 1 on SampleTable will now be associated with
 -- Partition 1 in Archive table.

 ALTER TABLE dbo.SampleTable
 SWITCH PARTITION 1
 TO dbo.SampleTable_Archive PARTITION 1

-- STEP 2:
 -- get smallest range vlaue from
 -- "select * from sys.partition_range_values ORDER BY boundary_id ASC"
 -- and MERGE it

 SELECT @merge_range = ( SELECT TOP 1 CONVERT(INT, value)
                         FROM sys.partition_range_values
                         ORDER BY boundary_id ASC)

 ALTER PARTITION FUNCTION pf_k_rows()
 MERGE RANGE (@merge_range)

-- STEP 3:
 -- get largest range vlaue from
 -- "select * from sys.partition_range_values ORDER BY boundary_id DESC"
 -- and SPLIT last range with a new value

 ALTER PARTITION SCHEME ps_k_rows
 NEXT USED [PRIMARY]

 SELECT @split_range = (SELECT TOP 1 CONVERT(INT, value)
                        FROM sys.partition_range_values
                        ORDER BY boundary_id DESC)
 SELECT @split_range = @split_range + 1000

 ALTER PARTITION FUNCTION pf_k_rows()
 SPLIT RANGE (@split_range)

-- STEP 4:
 -- TRUNCATE SampleTable_Archive to keep its partitions empty for
 -- SWITCH in next iteration
 --
 TRUNCATE TABLE dbo.SampleTable_Archive

 GO

-- EXECUTE dbo.sp_split_merge_SampleTable

Hope this helps,
_SqlTimes

Read Full Post »

Missing Jobs in Sql Agent Listing

I ran into something peculiar today.

I have a bunch of standard Sql Agent jobs on my Sql Server 2008.  Along with a few maintenance jobs (“Database Integrity Check “, ‘Cleanup History’, etc), there are some business process jobs i.e. ‘Purge’, ‘Expire Old Products’, ‘Process Reports’, etc. Some of these jobs are not listed in SSMS under ‘Sql Agent >> Jobs‘. They can be queried from ‘sysjobs’ table in MSDB database.

On further investigation, I see that all these un-listed [in SSMS (Sql Agent >> Jobs)] jobs are either in 0 or 4 category. These jobs were originally created when the application was on Sql Server 2000. But since then it was upgraded to Sql Server 2005 and Sql Server 2008. If you look at this MSDN article, only 3 categories are supported starting Sql 2005 i.e. 1, 2, 3.  So these jobs categories (0 and 4) are not part of the standard list in Sql Server 2005/2008/2012.

So my guess is that, because of this unexpected category, SSMS is not listing them under “Sql Agent >>  Jobs“. May be the query that SSMS runs to list out jobs is something like “SELECT * FROM msdb.sysjobs WHERE category_id IN (1, 2, 3)”. So it is not displaying these jobs with different categories.

Solution:

For the jobs that are not displayed is SSMS, UPDATE  the category column to 3.

UPDATE dbo.sysjobs
SET category_id = 3
WHERE job_id = '285A30B2-11C9-4B54-8B4D-48E48A059426'
AND category_id = 4
GO

Hope this helps,

_SqlTimes

Read Full Post »