Blog: How to remove the Management Data Warehouse from SQL Server running on a production instance

One of our customers had their Management Data Warehouse (MDW) running on their production SQL Server 2012 and didn’t know what to do with it. Microsoft had introduced the MDW with SQL Server in 2008. However, there was a bug in SQL Server 2012 that prevented the Query Statistics data collector from working. Rather than trying to fix it, we made the decision to remove it instead. We replaced the MDW with the Open Query Store project, a community supported tool that works with SQL Server 2008 and higher.

The problem

Removing the MDW from SQL Server running in production is straight forward and requires that you follow the steps below.

The solution

Disable the sysutility jobs

First, you need to disable the following three SQL Agent jobs:

• sysutility_get_cache_tables_data_into_aggregate_tables_daily
• sysutility_get_cache_tables_data_into_aggregate_tables_hourly
• sysutility_get_views_data_into_cache_tables

The scripts to use to disable these jobs are:

Steps for removing the MDW

To remove the actual MDW, use the following steps:

1. Run the command

   1	EXEC msdb.dbo.sp_syscollector_cleanup_collector

2. Perform full copy-only backup for MDW database (optional)
3. Remove MDW database with the following command

   1 2 3

   USE master; GO DROP DATABASE MDW;

4. Execute the following command to delete the purge data job

   1	EXEC msdb.dbo.sp_delete_job @job_name = N'mdw_purge_data_[MDW]';

5. 5. Drop the SQL Agent jobs related to this MDW

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

   EXEC msdb.dbo.sp_delete_job @job_name = N'sysutility_get_cache_tables_data_ino_aggregate_tables_daily'; EXEC msdb.dbo.sp_delete_job @job_name = N'sysutility_get_cache_tables_data_ino_aggregate_tables_hourly'; EXEC msdb.dbo.sp_delete_job @job_name = N'sysutility_get_views_data_into_cache_tables'; EXEC msdb.dbo.sp_delete_job @job_name = N'collection_set_1_noncached_collect_and_upload'; EXEC msdb.dbo.sp_delete_job @job_name = N'collection_set_2_collection'; EXEC msdb.dbo.sp_delete_job @job_name = N'collection_set_2_upload'; EXEC msdb.dbo.sp_delete_job @job_name = N'collection_set_3_collection'; EXEC msdb.dbo.sp_delete_job @job_name = N'collection_set_3_upload'; EXEC msdb.dbo.sp_delete_job @job_name = N'collection_set_7_collection'; EXEC msdb.dbo.sp_delete_job @job_name = N'collection_set_7_upload';

And that’s it! Stay tuned to see how Open Query Store works to replace MDW functionality.