SQL SERVER – Fix Error: Currently This Report Does Not Have Any Data to Show, Because Default Trace Does Not Contain Relevant Information

One of my clients of Comprehensive Database Performance Health Check recently asked me if there is a way to fix the error which they get when they try to open Schema Changes History. The error was related to Not Have Any Data to Show in the report. Let us see how we can fix that.

Here is the screenshot of the error.

The error actually showed up when I went to DatabaseName >> Reports >> Standard Reports >> Schema Changes History. If you want to learn more about Schema Change History Report, you can read this blog post: SQL SERVER – SSMS: Schema Change History Report.

The solution of the same is very simple and I have previously blogged about it here: SQL SERVER – SSMS: Configuration Changes History.

Here is the solution:

EXEC sp_configure 'Show Advanced Options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1
GO
RECONFIGURE WITH override
GO

When you run the above command, SQL Server will first enable the advanced options. Right following that, it will enable the default trace. Once the above script is executed, it will give the following message.

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘default trace enabled’ changed from 1 to 1. Run the RECONFIGURE statement to install.

Now make some changes in the schema and right following it, run the report. You will see something like the following image.

Here are a few related blog posts on the same topic of any data:

• SQL SERVER – Performance Dashboard: Current Activity Section Reports
• SQL SERVER – SSMS: Performance Dashboard Reports – Home Page details
• SQL SERVER – Schema Change Reports – SQL in Sixty Seconds #078
• SQL SERVER – SSMS: Top Transaction Reports
• SQL SERVER – SSMS: Top Object and Batch Execution Statistics Reports
• SQL SERVER – Activity Reports – Dormant Sessions
• SQL SERVER – Introduction to SQL Server Management Studio Reports
• SQL SERVER – Standard Reports from SQL Server Management Studio – SQL in Sixty Seconds #016 – Video

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.