Querying Data Collector Retention Period in SQL Server MDW

management-data-warehousesql server

I have data collectors active on many remote servers and I am trying to find a query that will capture the retention periods for all data collectors active on an instance.

These are system data collectors, part of Management Data Warehouse, not custom collectors.

Any ideas which tables will hold this data?

Best Answer

Inside the database you selected for Management Data Warehouse there is a table called [core].[source_info_internal] that contains a column days_until_expiration

This query gives you the retention periods per collection set:

SELECT [collection_set_uid], [days_until_expiration]
FROM [core].[source_info_internal];

Returns:

+--------------------------------------+-----------------------+
|          collection_set_uid          | days_until_expiration |
+--------------------------------------+-----------------------+
| 2DC02BD6-E230-4C05-8516-4E8C0EF21F95 |                    14 |
| 49268954-4FD4-4EB6-AA04-CD59D9BB5714 |                    14 |
| 3006169E-D33A-4101-A1DE-9F0DABF7D84E |                    30 |
| B83A3832-37CD-4C7A-88F6-6696DA5A7E43 |                    30 |
| 7B191952-8ECF-4E12-AEB2-EF646EF79FEF |                   730 |
+--------------------------------------+-----------------------+

The collection set descriptions are unexpectedly in msdb so if you want the names of the collection set along with the retention period you could use:

SELECT msdb.[name],  mdw.[days_until_expiration]
FROM [core].[source_info_internal]  mdw
INNER JOIN [msdb].[dbo].[syscollector_collection_sets_internal] msdb
on mdw.[collection_set_uid] = msdb.[collection_set_uid];

which returns (on my system):

+---------------------------------+-----------------------+
|              name               | days_until_expiration |
+---------------------------------+-----------------------+
| Disk Usage                      |                   730 |
| Server Activity                 |                    14 |
| Query Statistics                |                    14 |
| Stored Procedure Usage Analysis |                    30 |
| Table Usage Analysis            |                    30 |
+---------------------------------+-----------------------+

Please note however that the Agent job that purges the data calls a stored procedure that accepts parameters so this query lists the default retention periods. If somebody changed it at the job step level you wouldn't get that information.

The procedure definition:

ALTER PROCEDURE [core].[sp_purge_data]
    @retention_days smallint = NULL,
    @instance_name sysname = NULL,
    @collection_set_uid uniqueidentifier = NULL,
    @duration smallint = NULL,
    @delete_batch_size int = 500
AS

So somebody could have overridden the default settings, you could look at the source code for that procedure to follow the entire logic.

Related Question