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
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.
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:
I have a question. Our CIO wants to have the ticket data in the data warehouse deleted after 3 years; however he wants to require manual intervention to do it. Is there a way to do that?