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!
The SQL Dude! 
Leave a Reply