By: Aaron Bertrand | Updated: 2012-07-31 | Comments (18) | Related: > Performance Data Warehouse
Problem
SQL Server 2008 introduced a new feature, Management Data Warehouse (MDW), which allows users to collect metrics on their servers over time to aid in performance troubleshooting. A lot of people try this feature out, because it is easy to set up, and then find that it is not so easy to remove. In fact, removing MDW is not supported; in SQL Server 2012, though, a new system stored procedure was added to make this process easier. The problem is that this stored procedure (as well as several of the workarounds I've seen published) can leave several objects behind.
Solution
In order to help understand how to completely remove MDW, I'm going to show the things that it installs, the guts of the new system procedure introduced in SQL Server 2012, what happens when you run that code on 2008, 2008 R2, or 2012, and how to eliminate some of the remnants it leaves behind.
What gets installed with MDW
In order to set up MDW, you open Management Studio, navigate to your server using Object Explorer, expand the Management node, right-click Data Collection and choose "Configure Data Management Warehouse." This will yield a welcome screen. Click Next. On the next screen, you choose "Create or upgrade a management data warehouse":
Click Next. On this screen, you pick your server (well, I believe you only ever have one choice here), then you can pick an existing database or create a new one. For simplicity, you should create a new one in this case, and name it MDW:
Click Next. On this screen, you can configure logins and users who can access the data in your MDW, but let's skip this step for now. Clicking Next will allow you to click Finish, and after a few seconds you should see a success dialog:
But we're not done yet. This merely created the MDW database and added a job called mdw_purge_data_[MDW], which is used to clean up the data over time. In order to actually collect data, we need to enable collection. Righ-click Data Collection and choose "Configure Data Management Warehouse" again. Click Next. This time choose the option to "Set up data collection":
Click Next. Here you'll have to pick the local instance again, and choose the MDW database, as well as specify a local cache directory for files to upload:
Click Next, then click Finish, and you should see another success confirmation dialog. We can inspect what has been added in Object Explorer (new items marked with red asterisks):
In SQL Server 2012, in addition to all of the above, there are three more jobs that get created, prefixed with sysutility:
So how do you remove this stuff? Well, you could right-click each Data Collection Set and choose "Stop Data Collection Set", then remove the jobs one by one, but I don't recommend that, mainly because it is tedious. You'll note that if you right-click "Data Collection" your only feasible option is "Disable Data Collection." This doesn't remove anything, it just (very slowly) disables each of the the collection set jobs (oddly, allowing you to continue using each of the enabled Data Collection Sets manually if you want to). Note that this does not disable the mdw_purge_data job or the sysutility jobs. And if you choose "Configure Data Management Warehouse" your only options are still to "Create or upgrade a management data warehouse" or "Set up data collection" - no hint, anywhere, of how to remove anything that you've set up.
The SQL Server 2012 solution
In SQL Server 2012, the stored procedure msdb.dbo.sp_syscollector_cleanup_collector was added to provide an alternative to the disclaimer-ridden code offered here. The code inside the procedure (which takes no parameters) looks roughly like this (I've only added the USE msdb; bit):
USE msdb; GO
-- Disable constraints -- this is done to make sure that constraint logic does not interfere with cleanup process ALTER TABLE dbo.syscollector_collection_sets_internal NOCHECK CONSTRAINT FK_syscollector_collection_sets_collection_sysjobs ALTER TABLE dbo.syscollector_collection_sets_internal NOCHECK CONSTRAINT FK_syscollector_collection_sets_upload_sysjobs
-- Delete data collector jobs DECLARE @job_id uniqueidentifier DECLARE datacollector_jobs_cursor CURSOR LOCAL FOR SELECT collection_job_id AS job_id FROM syscollector_collection_sets WHERE collection_job_id IS NOT NULL UNION SELECT upload_job_id AS job_id FROM syscollector_collection_sets WHERE upload_job_id IS NOT NULL
OPEN datacollector_jobs_cursor FETCH NEXT FROM datacollector_jobs_cursor INTO @job_id
WHILE (@@fetch_status = 0) BEGIN IF EXISTS ( SELECT COUNT(job_id) FROM sysjobs WHERE job_id = @job_id ) BEGIN DECLARE @job_name sysname SELECT @job_name = name from sysjobs WHERE job_id = @job_id PRINT 'Removing job '+ @job_name EXEC dbo.sp_delete_job @job_id=@job_id, @delete_unused_schedule=0 END FETCH NEXT FROM datacollector_jobs_cursor INTO @job_id END
CLOSE datacollector_jobs_cursor DEALLOCATE datacollector_jobs_cursor
-- Enable Constraints back ALTER TABLE dbo.syscollector_collection_sets_internal CHECK CONSTRAINT FK_syscollector_collection_sets_collection_sysjobs ALTER TABLE dbo.syscollector_collection_sets_internal CHECK CONSTRAINT FK_syscollector_collection_sets_upload_sysjobs
-- Disable trigger on syscollector_collection_sets_internal -- this is done to make sure that trigger logic does not interfere with cleanup process EXEC('DISABLE TRIGGER syscollector_collection_set_is_running_update_trigger ON syscollector_collection_sets_internal')
-- Set collection sets as not running state UPDATE syscollector_collection_sets_internal SET is_running = 0
-- Update collect and upload jobs as null UPDATE syscollector_collection_sets_internal SET collection_job_id = NULL, upload_job_id = NULL
-- Enable back trigger on syscollector_collection_sets_internal EXEC('ENABLE TRIGGER syscollector_collection_set_is_running_update_trigger ON syscollector_collection_sets_internal')
-- re-set collector config store UPDATE syscollector_config_store_internal SET parameter_value = 0 WHERE parameter_name IN ('CollectorEnabled')
UPDATE syscollector_config_store_internal SET parameter_value = NULL WHERE parameter_name IN ( 'MDWDatabase', 'MDWInstance' )
-- Delete collection set logs DELETE FROM syscollector_execution_log_internal
Running the code on SQL Server 2008
After a casual inspection it seemed to me that there was no reason the guts of this procedure couldn't be run on a SQL Server 2008 instance, so I tried it. It showed the following output:
And it seemed to largely put the MDW settings back to the way they were before MDW was ever configured. But as I mentioned before, it did leave several things behind. In SQL Server 2008:
- The MDW database, including data you've collected in tables such as
snapshots.disk_usage - The mdw_purge_data_[MDW] job, including its schedule (mdw_purge_data_schedule)
- The following schedules:
- CollectorSchedule_Every_5min
- CollectorSchedule_Every_10min
- CollectorSchedule_Every_30min
- CollectorSchedule_Every_60min
In SQL Server 2012, it also left additional items:
- The following jobs (and their associated schedules, which start with
Occurs):- sysutility_get_cache_tables_data_ino_aggregate_tables_daily
- sysutility_get_cache_tables_data_ino_aggregate_tables_hourly
- sysutility_get_views_data_into_cache_tables
- Additional collection schedules that weren't present in 2008:
- CollectorSchedule_Every_15min
- CollectorSchedule_Every_6h
To perform a more complete MDW removal
First, run the code from the 2012 stored procedure that I've copied above.
Next, decide what you want to do with the MDW database. I think I'm okay with it leaving the database behind; after all, you may just want to stop collecting data but still be able to analyze what you've collected. So, you could either just drop the database, or back the database up and then drop it at your leisure.
Next, you'll still need to manually remove the mdw purge job. On all versions of SQL Server:
EXEC msdb.dbo.sp_delete_job @job_name = N'mdw_purge_data_[MDW]'; -- replace MDW within the square brackets with the name you gave your MDW database
On SQL Server 2012 only, you may want to either drop or disable the sysutility jobs. Here is how you can disable them:
EXEC msdb.dbo.sp_update_job
@job_name = N'sysutility_get_cache_tables_data_ino_aggregate_tables_daily',
@enabled = 0;
EXEC msdb.dbo.sp_update_job
@job_name = N'sysutility_get_cache_tables_data_ino_aggregate_tables_hourly',
@enabled = 0;
EXEC msdb.dbo.sp_update_job
@job_name = N'sysutility_get_views_data_into_cache_tables',
@enabled = 0;And here is how to drop them (this should also drop their schedules):
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';You may have noticed that I did not yet provide code for you to delete the collection-related schedules. This was intentional: if you delete these, and try to enable data collection again, it will fail with the following error:
Unable to start collection set Disk Usage. Msg 14373, Level 16, State 1, Procedure sp_verify_schedule_identifiers, Line 29 Supply either @schedule_id or @schedule_name to identify the schedule.
The fact is that, while these schedules may look like they were created by MDW, they are created by default in a default installation of SQL Server 2008 or above. You can delete these easily, if you really want to, and if you are sure you will never enable MDW again. I would still suggest scripting the schedules and saving those scripts to your file system or source control so that you can recover them in the future.
EXEC msdb.dbo.sp_delete_schedule @schedule_name = N'CollectorSchedule_Every_5min'; EXEC msdb.dbo.sp_delete_schedule @schedule_name = N'CollectorSchedule_Every_10min'; EXEC msdb.dbo.sp_delete_schedule @schedule_name = N'CollectorSchedule_Every_30min'; EXEC msdb.dbo.sp_delete_schedule @schedule_name = N'CollectorSchedule_Every_60min';
-- on 2012 only: EXEC msdb.dbo.sp_delete_schedule @schedule_name = N'CollectorSchedule_Every_15min'; EXEC msdb.dbo.sp_delete_schedule @schedule_name = N'CollectorSchedule_Every_6h';
If you run this, please don't say I didn't warn you. The truth is I have no idea what other system functions might ultimately rely on these schedules, now or in the future. The disclaimer should probably also be made for the sysutility jobs, though a difference is that these jobs and their schedules are absolutely created when enabling MDW the first time.
Conclusion
Management Data Warehouse has never been easy to remove; even in SQL Server 2012, the procedure that Microsoft has provided takes care of some of the work, but not all. Using the steps outlined above, you can get that much closer to completely removing all traces of MDW from your system. Note that all of this could still potentially leave behind other items. For example, any logins, users, custom data collections or custom reports that were created explicitly for use with the MDW database. In reality, some of this cleanup might have to be manual by definition, as there is not any predictable way that one could programmatically determine what logins, for example, are intended only for MDW usage.
Next Steps
- Follow the above procedures for any instance where you still have remnants of MDW that you'd like to remove.
- Vote and comment on this Connect item, which aims to make this removal much more thorough. (There are also some other workarounds and blog posts mentioned there.)
- Review the following tips and other resources:
About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2012-07-31
