Monday, November 15, 2010

Removing the SQL Server Management Data Warehouse (or not!)

I have SQL Server Management Data Warehouses implemented on dozens of servers in various customer sites.  The data it collects is great, especially considering that it's free and easy to implement (initially).  But as with most free things, it has some drawbacks.  Most of all, sometimes it's not very reliable.  In some environments, I spent more time chasing down issues with the SSIS packages it runs than reviewing the data it collects.  Some of my clients have chosen to invest in commercial monitoring tools such as Quest Spotlight, Idera Diagnostic Manager, SQL Sentry, Redgate SQL Monitor or Confio Ignite.  Each of these tools has pluses and minuses, but overall they are all more reliable than the Management Data Warehouse. 

The net result is that I'm working to decommission Management Data Warehouses in various environments.  Unfortunately, I discovered that Microsoft never implemented a removal utility for the Management Data Warehouse.  Without manual intervention, the best you can do is disable it.  However, it leaves it's jobs behind, which in some cases had a last status of Failed.  This clutters up my reporting and it drives me batty.

Initially I cleared the job failures so that they wouldn't show up in any more reports.  Now I'm looking at how to remove the jobs and objects completely.   Sadly, Microsoft still hasn't published information about this.  DBAs are complaining, but apparently it isn't a big priority.

So, that leaves the manual steps of removing the fragments of the Management Data Warehouse from the server.  The removal workaround documented on the support site isn't supported, nor was it recommended by Microsoft.  Moreover, it references a table that doesn't exist.

I've decided to live with it wait and see what Microsoft puts out in a future service pack before attempting a crude hack that might make it impossible to fully remove the feature when Microsoft finally gets around to this issue.


  1. Microsoft added a cleanup procedure in SQL Server 2012.
    If you use this procedure on a SQL Server 2008 R2 installation it works properly

  2. Do you know where that cleanup procedure is, Christian?

  3. Data warehouse development is a serious undertaking, often time-consuming and costly. It pays to be prepared, and to approach it with dashboard

  4. There are various type of data scientist available in the marketplace, however the finding of best data scientist is not easy for you because you don't about the info related problem and its solution, you could check here the original source Active Wizards the variety of different data handling expert who can help you to solve your all kind of data related problems.

  5. Its my great pleasure to visit your blog and to enjoy your great posts here. I like it a lot. I can feel that you paid much attention for those articles, as all of them make sense and are very useful.  vpnveteran

  6. A good blog always comes-up with new and exciting information and while reading I have feel that this blog is really have all those quality that qualify a blog to be a one.  diebestenvpn