The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

Update to the MDW (Data Collector) Post

Posted by Sudarshan Narasimhan on January 13, 2012


Back in August 2011, I had posted quite a lengthy blog post on Management Data Warehouse (Data Collector). I made an update to that post today to include a new issue and the fix for that issue. This has also been updated in the official KB article given below. For those of you out there using Data Collector to monitor your SQL Server’s, please read this to avoid any future issues.

FIX: Data Collector job takes a long time to clear data from a MDW database in SQL Server 2008 R2
http://support.microsoft.com/kb/2584903

To break it down (or) putting it simply, here is what you need to do as a DBA IF you are using MDW or Data Collector

1. For SQL Server 2008 R2, please apply Service Pack 1 immediately.
2. Follow KB 2584903 (or) my earlier post to modify the stored procedure sp_purge_orphaned_notable_query_text.
3. Run the purge procedure ad-hoc or let it run on schedule and this will actually do the clean-up and reduce the size of the MDW database.

The reason for step#2 even after applying SP1, is because a new bug in the T-SQL code was found. i.e. the delete statement incorrectly references the snapshots.notable_query_plan table after you apply Service Pack 1, and we need to change this to correctly reference the snapshots.notable_query_text table.

Have fun!

Advertisements

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: