Share via

Removing SQL Server Management Data Warehouse (Data Collector)

  • Article

Introduction

The data collector is a component present in SQL Server since the 2008 version and allows us to collect information from different servers for further analysis. All information collected is stored in a database called Management Data Warehouse and reports can be run against this data. The data collector solution is widely used because it presents a lot of information through their reports, another point is the fact that it is easy to set up. The problem occurs when you try to completely remove the solution from your SQL Server environment.

Removal of the MDW (Management Data Warehouse) is not supported in 2008 and 2008 R2 versions, but in SQL Server 2012 a new Stored Procedure was added to assist in this point and can be run on earlier versions also. Let's move on to understanding what is created during the configuration process and how to remove as much of components that are added to your environment.

Structure

After the creation of the Management Data Warehouse a job called "mdw_purge_data_ [MDW]" is created, this object has the function to purge the data periodically. After configuring the instance to have the data collected, there are another five jobs (collectors) are created:

  • “collection_set_1_noncached_collect_and_upload”
  • “collection_set_2_collection”
  • “collection_set_2_upload”
  • “collection_set_3_collection”
  • “collection_set_3_upload”

For SQL Server 2012 three additional jobs are included:

  • “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”

Removal

Let's try to remove the MDW Jobs intuitively. 

Go to each collector and stop the collection set.

http://www.sqlservercentral.com/Images/23552.jpg

After this process the Jobs are disabled except the "mdw_purge_data" and "sysutility" jobs. You can still disable the Data Collector himself going in the component properties.

http://www.sqlservercentral.com/Images/23551.jpg

Following the attempt to delete the Jobs that are no longer used, you notice that this is not possible due to a number of constraints within the MSDB table system.

http://www.sqlservercentral.com/Images/23553.gif

Apparently the only way to exclude the Jobs would be by these steps that were made, because in the MDW configuration interface is there no option to perform any type of removal in SQL Server versions specified above.

Solution

In SQL Server 2012, Microsoft has added the Stored Procedure "msdb.dbo.sp_syscollector_cleanup_collector" to allow the removal some components of the Data Collector, outlining the lack of this feature in the configuration interface.

The script inside the Stored Procedure is this:

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

After running the script against SQL Server 2008, the following output was returned:

"Removing job collection_set_2_collection

Removing job collection_set_2_upload

Removing job collection_set_3_collection

Removing job collection_set_3_upload

Removing job collection_set_1_noncached_collect_and_upload

(4 row(s) affected)

(4 row(s) affected)

(1 row(s) affected)

(2 row(s) affected)

(53 row(s) affected)"

This result shows that many rows were affected and these five jobs were removed:

  • “collection_set_1_noncached_collect_and_upload”
  •  “collection_set_2_collection”
  • “collection_set_2_upload”
  • “collection_set_3_collection”
  • “collection_set_3_upload”

This Stored Procedure is not able to eliminate all components created by MDW, the job "mdw_purge_data_ [MDW]" have to be excluded manually and for SQL Server 2012 version also have the additional jobs "sysutility " to remove. Existing Schedules in SQL Server can also be excluded, but would not recommend, because although it appears that were created with the MDW, they are added by default in the SQL Server installation and are necessary for the future operation of the MDW, if they want to re-enable to use again. 

Conclusion

We can see that the Management Data Warehouse is not easy to remove in any of the versions. Although we use a script as part of the solution, there are still issues that can be forgotten as logins and users.