MSSQL Data collection "SQL Server activity" is empty

Question

I'm having an issue with the MSSQL Data colleaction "SQL Server activity" graph. It is empty and not showing any information (screenshots below):

The graph is not visible, but the area is click-able: Screenshot - http://i.imgur.com/7XTV9xi.png

This is showing when I click on the graph: Screenshot - http://i.imgur.com/DnaS2XZ.png

So far i've tried to perform the following troubleshooting steps:

Manually collect and upload the data + Stop and start the collection + Clear the Data collection cache files + Search the job history for errors - there aren't such

Answer 1

Please try extending the logging of the MDW jobs, using logging_level column for each collection (for reference, please see http://www.mssqlinsider.com/2012/11/enable-additional-logging-for-sql-server-management-data-warehouse-collection-sets/)

Another option is to have some issues with the data, so an opt here is to just check the actual MDW reports and the datasets behind so you can find the part that is not returning data. Sources of the MDW rdl files can be found here: http://blogs.msdn.com/b/billramo/archive/2010/11/18/may-the-source-be-with-you-mdw-report-series-part-6-the-final-edition.aspx

Good luck!

Answer 2

I resolved this issue in my environment by resetting the identity seed on the performance_counter_instances table.

The package uses a unique index with ignore_dup_key to build the dimension, but I don't think they realized that the identity would still increment even though the rows are discarded.

Run dbcc checkident on the table and make sure it's not overflowing the int column.

Answer 3

Had the same problem with 2 SQL Server 2012 installation - no errors logged, but empty Server Activity History screen. The following commands solved the error (as suggested above - was not able to comment due to not enough credits of my account)

USE MDW ;
GO
dbcc checkident('snapshots.performance_counter_instances') ;
GO