Change the Data Warehouse retention period

  • Article

A customer of mine wanted the Data Warehouse retention changed from the 3 to 5 years, for reporting purposes.

To see the current retention settings of the DW databases, you can use the cmdlet that comes with SCSM.

Get-SCDWRetentionPeriod
NOTE: if not using the correct syntax, it returns the setting of the DWRepository DB and not the DWDataMart.

So to get the retention setting for the DWDatamart you use the following syntax:

Get-SCDWRetentionPeriod –ComputerName <Data Warehouse Management Server name> –DatamartComputerName <SQLServer\SQLInstance> -DatamartDatabaseName DWDataMart

e.g. Get-SCDWRetentionPeriod –ComputerName SCSM-DW –DatamartComputerName SCSM-SQL\DW -DatamartDatabaseName DWDataMart

clip_image002

You can also change the settings for the OMDWDataMart and CMDWDataMart this way. But my customer is only interested in the DWDataMart data.

So to change the retention of the DWDatamart to 5 years ( 5 years * 365 days * 24 hours * 60 minutes = 2628000 minutes) the commandline is:

Set-SCDWRetentionPeriod –ComputerName SCSM-DW –DatamartComputerName SCSM-SQL\DW -DatamartDatabaseName DWDataMart -DurationInMinutes 2628000

After running the command and rerun the Get-SCDWRetentionPeriod cmdlet, you will see the new retention period in minutes.

clip_image002

Remember to estimate the size of the DWDataMart when storing data for 2 more years.

If you want to run a SQL Query to verify the retention setting, you can do like this:

use DWDataMart

(select etl.GetConfigurationInfo('dwmaintenance.grooming', 'RetentionPeriodInMinutes.Default'))

Big kudos to Mihai Sarbulescu for his guidance.