Sample Header Ad - 728x90

mdw_purge_data_ long runtime

2 votes
1 answer
565 views
I have a SQL Server 2008 R2 that is running several databases and has also been configured as our central MDW. When the management data warehouse is created it creates a number of agent jobs one of which is mdw_purge_data_[DB NAME] that goes through and purges data that exceeds the retention period, as far as I am aware. This job is currently running and its duration has increased form a few hours to days. This was initially an issue in RTM but was supposedly fixed in Service Pack 1 (we are running SP2). I have tried following an amendment of the stored procedure it calls as per this link: http://www.toddbaker.org/blog/2010/12/17/sql-2008-mdw-fixing-long-running-purges/ This does not seem to have helped. I then read another blog post suggesting creating two indexes on the sql_handles columns of two tables in the MDW, snapshots.query_stats and snapshots.notable_query_text. When I try and create these I end up with a timeout and the index fails to create. This suggests that there is far too much information in there. Has anyone got any pointers about reigning in the mdw_purge_data_ job as I think it may well be impacting the performance of the server as a whole.
Asked by user20442
May 9, 2013, 10:21 AM
Last activity: May 13, 2016, 05:38 AM