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

May 26, 2020

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.

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

Here is the screenshot of the error.

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

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.

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