System Center 2012 Service Manager Data Warehouse Retention Settings

System Center 2012 Service Manager Data Warehouse Retention Settings

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

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


Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.