Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Removing the SQL Server Management Data Warehouse

MSSQLTips author Aaron Bertrand By:   |   Read Comments (15)   |   Related Tips: More > 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":

MDW Config task screen

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:

Create a new MDW database

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:

Success creating MDW database

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":

MDW Config task screen 2

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:

MDW Storage

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):

New MDW objects in SQL Server 2008

In SQL Server 2012, in addition to all of the above, there are three more jobs that get created, prefixed with sysutility:

Additional MDW jobs in SQL Server 2012

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:

Output after running cleanup code

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


Last Update: 7/31/2012


About the author
MSSQLTips author Aaron Bertrand
Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for over a decade, first earning the Microsoft MVP award in 1997.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, November 18, 2014 - 6:07:57 AM - Dan Lunnon Read The Tip

There is a missing t in some of the job names.

data_ino_aggregate

data_into_aggegate


Monday, August 12, 2013 - 8:30:11 AM - Paul Willson Read The Tip

Great article, thank you.

Some positive feedback for you.

We just upgraded our cluster to new hardware. sql server 2008 r2 (old and new).

Prior to cutover the new cluster had, obviously, a different name to the production server. (Prod/bi - new/bi)

As part of the exercise, we restored msdb across from old.

We then set up MDW on new/bi , which worked fine , until we renamed the new server to that of the production environment.

At cutover, the old server was renamed, then the new server given the production server name. The sql instance name was the same on both environments (BI)

Data collection then started to fail because of the server name change.

We used the above scripts to clean out mdw, but when it was set up again, it still failed being unable to connect to sql.

We had to add one more task, and that was to clear out any reference to collection from [msdb].[dbo].[sysssispackages].

Namely, any row that referenced collection queries , eg 'TSQLQuery_old_MSSQL10_50_BI_{7B191952-8ECF-4E12-AEB2-EF646EF79FEF}_2_Collect'.

We found that the rows were not updated on configuration of mdw, so still referenced the old server AND the new (due to msdb restore).

 

After that, set up created new rows, which were then correct.

 

 


Thursday, July 11, 2013 - 9:08:47 AM - Aaron Bertrand Read The Tip

Regena, are you saying this symptom is already happening, or started happening after you cleaned up MDW? What version of Management Studio are you running (see Help > About)? Against what version of SQL Server (use SELECT @@VERSION in a query window)?


Wednesday, July 10, 2013 - 2:44:04 PM - Regena Read The Tip

Management in SQL will not expand .  It says invalid object name 'dbo.syscollector_config_store_internal'

could not use view or function msdb.dbo.syscollector_config_sotre' because of bindin errors.

 

 


Wednesday, May 22, 2013 - 9:14:58 AM - Anders Pedersen Read The Tip

Script worked great.  Ran it on a SQL 2008 R2 server.  However, the 3 jobs you have marked as SQL 2012 only was also on this server, which is a little strange....

 

Also, the 2 first of those drop statements have an error in the job name, listed as _int_ instead of _into_


Tuesday, April 09, 2013 - 3:33:30 PM - Jim Read The Tip

Spoke too soon - The contraints appear to be enabled too soon in the process.  If the contraints are enabled after the update of the collection_job_id and upload_job_id in table syscollector_collection_sets_internal, they can be enabled WITH CHECK.

-- Update collect and upload jobs as null
UPDATE syscollector_collection_sets_internal
SET collection_job_id = NULL, upload_job_id = NULL 


Tuesday, April 09, 2013 - 3:18:33 PM - Jim Read The Tip

Apparently it is by design as the alter table fails when enabling the constraint WITH CHECK.

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_syscollector_collection_sets_collection_sysjobs". The conflict occurred in database "msdb", table "dbo.sysjobs", column 'job_id'.

 

Tuesday, April 09, 2013 - 11:48:13 AM - Jim Read The Tip

Great article.  Very helpful.  Do you think Microsoft is intentionally enabling the FK constraints on syscollector_collection_sets_internal as untrusted or is that a bug in their procedure?  They would need to add the WITH CHECK to re-enable the contraints as trusted.  On a clean install they start out trusted.  Thanks.

ALTER TABLE dbo.syscollector_collection_sets_internal
  WITH CHECK CHECK CONSTRAINT FK_syscollector_collection_sets_collection_sysjobs

ALTER TABLE dbo.syscollector_collection_sets_internal
  WITH CHECK CHECK CONSTRAINT FK_syscollector_collection_sets_upload_sysjobs


Friday, April 05, 2013 - 9:23:09 PM - Brady Read The Tip

Thanks Aaron!


Thursday, February 28, 2013 - 11:23:07 AM - Francis Read The Tip

Nice work. Thank you!


Wednesday, September 19, 2012 - 8:40:26 AM - Cool Robert Read The Tip

Thank you so much!  I was just cleaning up a production server where someone had started but not finished configuring this IN PRODUCTION, and was having a bit of difficulty cleaning everything up!

This was great!


Tuesday, September 18, 2012 - 4:32:14 PM - Jose Read The Tip

Great article, 

Thanks for sharing.

Just one small thing. My SQL 2008 Developer instance does not create the collection related schedule jobs, I see nothing there. Not that I am planning or was planning to delete those, but I wonder if that depends of the SP or hotfix you run.

 

Thanks,


Thursday, September 06, 2012 - 5:17:58 PM - Dan Read The Tip

This was helpful. Thank you.


Tuesday, August 21, 2012 - 7:30:54 AM - Ned Read The Tip

Thanks for posting this, it saved me a lot of time!


Tuesday, July 31, 2012 - 8:37:31 AM - John Read The Tip

I just did this and it worked great!  Thanks so much for posting this!




 
Sponsor Information