Today I finally found some spare time to dig into some SQL Server management data warehouse issues we were experiencing. The collection jobs failed for no visible reason and with (believe me) no meaningful error in the logs. So I wanted to enable some additional logging and while searching, I found this (I lost the link, so I cannot reference it :\ … )
If you want to enable additional logging for any of the collection sets you are running, you can use the below statement.
update msdb.dbo.syscollector_collection_sets set logging level = 2 where collection_set_id = <set id>
If you are wondering what set_id to use, the default ones are:
- Server Activity – 1
- Disk Activity – 2
- Query Activity – 3
- Utility information – 4
The rest can be discovered using the msdb.dbo.syscollector_collection_sets view.
The difference in logging is that actually if you enable advanced logging, you will be able to see the output of each single component of the SSIS package, used to run the upload jobs for example.