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.
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:
For SQL Server 2012 three additional jobs are included:
Let's try to remove the MDW Jobs intuitively.
Go to each collector and stop the collection set.
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.
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.
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.
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:
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.
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.