Q: How can I change the retention settings of my System Center 2012 Service Manager data warehouse?
A: System Center 2012 Service Manager (SCSM) features a data warehouse for long-term data retention. By default, most data is stored for three years (although some data is stored for 50 years), and the three years is set by a global value that can't be modified. Microsoft discusses retention settings in a TechNet blog post. However, I've tweaked some of the SQL to make it easier to check the retention period for all the tables:
SELECT WarehouseEntityName ,we.warehouseEntityID ,RetentionPeriodInMinutes FROM etl.WarehouseEntity (nolock) we JOIN etl.WarehouseEntityType (nolock) wet on we.WarehouseEntityTypeId = wet.WarehouseEntityTypeId JOIN etl.WarehouseEntityGroomingInfo (nolock) weg on we.WarehouseEntityId = weg.WarehouseEntityId WHERE wet.WarehouseEntityTypeName = 'Fact'
If, for example, in a lab environment you wanted to change the value for all the entities that currently use three years, you could use the following (which just changes retention to keep data for three years and a hour):
UPDATE etl.WarehouseEntityGroomingInfo SET RetentionPeriodInMinutes = 1576860 WHERE RetentionPeriodInMinutes = 1576800