Datawarehouse Database Cleanup SQL query

Real world problems with good old SCOM 2012 R2

Scompanion

IMPORTANT: Always perform a FULL Backup of the database before doing anything to it !!!

This article applies to SCOM 2007 and SCOM 2012

Somtimes you may have event storms where you end up having old entries in the Data Warehouse database i.e data that is older than the grooming threshold. This may happen beacuse the grooming processes can’t keep up because they run on a regular interval but only delete a fixed number of rows per run.

The following SQL query may also be valuable in case you end up with the issue of SQL Timeouts from the Data Warehouse database when the StandardDataSetMaintenance stored procedure is executed by the RMS.

More on that issue here: http://blogs.technet.com/b/kevinholman/archive/2010/08/30/the-31552-event-or-why-is-my-data-warehouse-server-consuming-so-much-cpu.aspx

To check if this is the case for you, run this SQL Query on the Data Warehouse database:

DECLARE    @MaxDataAgeDays INT,    @DataSetName NVARCHAR(150) SET @DataSetName = ‘Event’ SELECT @MaxDataAgeDays = MAX(MaxDataAgeDays) FROM StandardDatasetAggregation WHERE DatasetId =…

View original post 221 more words

Advertisements
About

IT professional with wider interest in technology

Posted in Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: