Removes data from the management data warehouse based on a retention policy. This procedure is executed daily by the mdw_purge_data SQL 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.
Applies to: SQL Server (SQL Server 2008 through current version).
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.
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