Have you ever wondered how to quickly find out what changes were made to your SQL Server configuration? In this blog post, we will explore a handy feature in SQL Server Management Studio (SSMS) that allows you to view the configuration change history.

Imagine you have just started a new job and you are responsible for managing SQL Server instances. You want to ensure that the servers adhere to certain standards, but you don’t know where to begin. That’s where the Configuration Change History report comes in.

The Configuration Change History report is available in SSMS and can be accessed by navigating to the Server node and selecting “Standard reports”. It is the second report in the list.

This report provides two key pieces of information:

1. Changes made to Server Configuration using sp_configure
2. Changes to trace flags (Enable or Disable) done via T-SQL

The information displayed in this report is fetched from the “default trace” which runs by default in every SQL Server installation. However, if the default trace is disabled by the administrator, the report will show a message stating that there is no data to display.

To enable the default trace, you can use the following command:

sp_configure 'default trace enabled', 1
GO
RECONFIGURE WITH OVERRIDE
GO

It’s important to note that in most installations, the default trace is already enabled by default, so you don’t need to manually enable it.

Now, let’s take a look at how the Configuration Change History report works behind the scenes. The report uses the function fn_trace_gettable to read the default trace and retrieve event classes 22 and 116.

SELECT name FROM sys.trace_events WHERE trace_event_id IN (22, 116)

These event classes correspond to changes made using DBCC TraceStatus or sp_configure, which are recorded in the default trace under the “ErrorLog” and “Audit DBCC Event” categories.

When you view the Configuration Change History report, additional filters are applied to show only relevant information, making it easier for you to analyze the changes made to your SQL Server configuration.

So, the next time you need to quickly find out what changes were made to your SQL Server configuration, don’t forget to check out the Configuration Change History report in SSMS. It’s a powerful tool that can help you track and manage your server configurations effectively.

Have you discovered any interesting information using this report? Let me know in the comments!