• Article

core.sp_purge_data (Transact-SQL)

Removes data from the management data warehouse based on a retention policy. This procedure is executed daily by the mdw_purge_dataSQL Server Agent job against the management data warehouse associated with the specified instance. You can use this stored procedure to perform an on-demand removal of data from the management data warehouse.

Topic link iconTransact-SQL Syntax Conventions

Syntax

core.sp_purge_data
    [ [ @retention_days= ] retention_days ]
    [ , [ @instance_name = ] 'instance_name' ]
    [ , [ @collection_set_uid = ] 'collection_set_uid' ]
    [ , [ @duration = ] duration ]

Arguments

  • [@retention_days =] retention_days
    The number of days to retain data in the management data warehouse tables. Data with a time stamp older than retention_days is removed. retention_days is smallint, with a default of NULL. If specified, the value must be positive. When NULL, the value in the valid_through column in the core.snapshots view determines the rows that are eligible for removal.

  • [@instance_name = ] 'instance_name'
    The name of the instance for the collection set. instance_name is sysname, with a default of NULL.

    instance_name must be the fully qualified instance name, which consists of the computer name and the instance name in the form computername\instancename. When NULL, the default instance on the local server is used.

  • [@collection_set_uid = ] 'collection_set_uid'
    The GUID for the collection set. collection_set_uid is uniqueidentifier, with a default of NULL. When NULL, qualifying rows from all collection sets are removed. To obtain this value, query the syscollector_collection_sets catalog view.

  • [@duration = ] duration
    The maximum number of minutes the purge operation should run. duration is smallint, with a default of NULL. If specified, the value must be zero or a positive integer. When NULL, the operation runs until all qualified rows are removed or the operation is manually stopped.

Return Code Values

0 (success) or 1 (failure)

Remarks

This procedure selects rows in the core.snapshots view that qualify for removal based on a retention period. All rows that qualify for removal are deleted from the core.snapshots_internal table. Deleting the preceding rows triggers a cascading delete action in all of the management data warehouse tables. This is done by using the ON DELETE CASCADE clause, which is defined for all the tables that store collected data.

Each snapshot and its associated data are deleted within an explicit transaction and then committed. Therefore, if the purge operation is manually stopped, or the value specified for @duration is exceeded, only the uncommitted data remains. This data can be removed the next time the job runs.

The procedure must be executed in the context of the management data warehouse database.

Permissions

Requires membership in the mdw_admin (with EXECUTE permission) fixed database role.

Examples

A. Running sp_purge_data with no parameters

The following example executes core.sp_purge_data without specifying any parameters. Therefore, the default value of NULL is used for all parameters, with the associated behavior.

USE <management_data_warehouse>;
EXECUTE core.sp_purge_data;
GO

B. Specifying retention and duration values

The following example removes data from the management data warehouse that is older than 7 days. In addition, the @duration parameter is specified so that the operation will run no longer than 5 minutes.

USE <management_data_warehouse>;
EXECUTE core.sp_purge_data @retention_days = 7, @duration = 5;
GO

C. Specifying an instance name and collection set

The following example removes data from the management data warehouse for a given collection set on the specified instance of SQL Server. Because @retention\_days is not specified, the value in the valid_through column in the core.snapshots view is used to determine the rows for the collection set that are eligible for removal.

USE <management_data_warehouse>;
GO
-- Get the collection set unique identifier for the Disk Usage system collection set.
DECLARE @disk_usage_collection_set_uid uniqueidentifier = (SELECT collection_set_uid 
    FROM msdb.dbo.syscollector_collection_sets WHERE name = N'Disk Usage'); 

EXECUTE core.sp_purge_data @instance_name = @@SERVERNAME, @collection_set_uid = @disk_usage_collection_set_uid;
GO