Whenever there is an issue with a SQL Server instance, the first question that would come to a DBA’s mind is “What changed on the instance?”. Then the DBA would check if someone did some changes to the SQL Server configuration using sp_configure or enabled some new trace flags.
In SQL Server 2000, there was no way to figure this out except for scanning through the SQL Server Error Logs or through the Blackbox trace if it was configured. Since the Blackbox trace would not have been configured on most of the instances, the only real option left was the SQL Server Error Log. But SQL Server in itself did not have a direct way of letting know the DBA about the changes to the configuration.
SQL Server 2005 was a welcome change in many ways. One of them was the introduction of Default Trace. This feature was an instant hit among the DBAs and the Developers alike. It traces the DDL changes as well as the changes to the SQL Server configuration along with many other information. Since I am talking about Configuration Changes in this post, let me explain how the Default Trace provides that information.
First we need to check if the Default trace is enabled on the instance.
sp_configure 'default trace enabled'
If the config_value is 1 in the output of this query, then it is enabled.
If it is not enabled the below query will enable it.
sp_configure 'show advanced options', 1 GO RECONFIGURE GO sp_configure 'default trace enabled', 1 GO RECONFIGURE GO
When the default trace is running, it will create .trc files in the defaut log directory for SQL Server. The contents of this trace file can be read using fn_trace_gettable function. But SQL Server already has built in Reports which does the same task and does a good job in presenting the data in an easy to read format. In SSMS, the Reports menu (right click on the Instance name) has several such reports.
One of those reports is Configuration Changes History. As the name suggests this is the report which gives the Configuration changes made in the recent past.
Since this report is generated from the Default trace, if the .trc files are missing or the trace files get rolled over (by default SQL Server keeps the last 5 trace files in the log directory), the report might not give the actual data. But in most cases, the content of the report helps in getting a sneak peek of the recent changes in the configuration. Here is an example from one of the instances where the Max Degree of Parallelism was changed and it has whodunnit info as well.
These reports are very useful when one is running short of time to get the information or feeling too lazy to write the script to read the trace files 😉