Microsoft SQL Server 2008 R2 Unleashed- P151:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1444 CHAPTER 39 Monitoring SQL Server Performance USE msdb GO EXEC GO EXEC GO To force the running of a noncached collection set and have it upload to the MDW for collection sets configured in noncached collection mode use the sp_syscollector_run_collection_set system procedure sp_syscollector_run_collection_set @collection_set_id collection_set_id @name 1 name1 You can pass either the collection set ID or the collection name. When you are passing one the other parameter can be NULL USE msdb GO EXEC sp_syscollector_run_collection_set @name Disk Usage go To force a manual update of a cached mode Data Collector you can use the sp_syscollector_upload_collection_set procedure USE msdb GO EXEC sp_syscollector_upload_collection_set @name Server Activity go To stop or start a specific collector set you can use the sp_syscollector_start_collection_set and sp_syscollector_stop_collection set stored procedures USE msdb GO EXEC @name Disk Usage GO EXEC @name Disk Usage GO To modify a collection set you can use the sp_syscollector_update_collection_set procedure. The syntax is as follows sp_syscollector_update_collection_set @collection_set_id collection_set_id @name name @new_name new_name Performance Monitoring Tools 1445 @target target @collection_mode collection_mode @days_until_expiration days_until_expiration @proxy_id proxy_id @proxy_name proxy_name @schedule_uid schedule_uid @schedule_name schedule_name @logging_level logging_level description description If the collection set is running the only options you can modify are the schedule_uid and description. You need to stop the collection set with sp_syscollector_stop_collection_set first to change other options like the collection-mode or days_until_expiration. For example the following code changes the number of days to retain collection set data to seven days for
