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:


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.


So finally sorted this out. There was an orphaned instance within the MDW. I removed this, which took at least a day to kill off. Trying to create the indexes failed through the gui due to a time out. Using activity monitor I could see that there were still huge waits on the server. I stopped all data collection for the MDW and disabled all the jobs associated, including the purge job. I then stopped this job as it was still running. Created the indexes using t-sql. Re ran the jobs and I am now back with the baseline for this server. I have gone from over a day to purge data to about 15 minutes so all in all much happier.

  • 2
    How did you identify and then remove the orphaned instance from MDW? May 13 '16 at 5:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.