Skip to content

sp_tblCleanupRetentionWindow

Jump to bottom
Pavel Pawlowski edited this page May 18, 2021 · 2 revisions

Table of Contents

Clone this wiki locally

sp_tblCleanupRetentionWindow

Drops all partitions in all affected tables prior retention window

The stored procedure takes the @pfName parameter which identifies partition function used by partitoned table(s). Then based on the @retentionWindow parameter it identifies partition containing the @retentionWindow value.

The @retentionWindow identifies partitioning value which must be kept. All values prior that one can be cleared. If the @retentionWindow value is part of the first (leftmost) partition, the procedure ends and does not perform any cleanup.

If the @retentionWindow is part of second and higher partitions, it scans for all Partition Schemes using the @pfName partition fucntion. Then it iterates through all tables using the partitions schemes.

Each partition prior the partition containing the @retentionWindow value in each table using the @pfName partition function is then TRUNCATED.

After the truncation the procedure merges all the partition prior the partition containing the @retentionWindow value to the first (leftmost) partition.

It keeps always the first left most partition empty

Usage

sp_tblCleanupRetentionWindow [parameters]

Parameters

Parameter Data Type Default Description
@pfName nvarchar(128) NULL Name of the partition function. All associated partition schemes and tables will be cleaned according specified @retentionWindow
@retentionWindow sql_variant NULL
  • Specifies retention window
  • All partitions prior the partition containing the @retentionWindow value will be cleared.
  • If partitions contain data, partitions are first TRUNCATED
  • All partitions prior the partition containing the @retentionWindow will be merged to to the first (leftmost) partition.
  • sp_tblCleanupRetentionWindow keeps first (leftmost) partition empty.
  • sp_tblCleanupRetentionWindow does not invoke any cleanup, if the @retentionWindow is part of the first (leftmost) partition.
  • @retentionWindow must be of the same exact data type as the partition function.
@infoOnly bit 1 When 1 prins only information about affected tables and partitions and does not perfrom any cleanup. When 0 does the actual cleanup.

Samples

Cleanup all tables associated with the pf_partitionByDate partition function. Cleanup and merge all partitions prior partition containing value of @retentionWindow = '2021-05-01'. pf_partitionByDate is using date data type

DECLARE @retentionWindow date = '20201-05-01'

EXEC [sp_tblCleanupRetentionWindow]
    @pfName               = 'pf_PartitionByDate'
    ,@retentionWindow     = @retentionWindow
    ,@infoOnly            = 0