SQL Server Management Studio Standard Reports – Configuration Changes History

SQL Server Management Studio Standard Reports – Configuration Changes History

Rate This
  • Comments 3

I’m continuing a series on the Standard Reports you can find in SQL Server Management Studio (SSMS).

(Note: These reports only work against SQL Server 2005 and higher. They don’t run against the lower versions because those don’t have all the management objects the reports depend on.)

The Configuration Changes History report shows the changes you’ve made to an instance using two mechanisms: 

1.       The sp_configure stored procedure (more about that here)

2.       The Default Trace mechanism, watching for Trace Flag events

The sp_configure stored procedure lets you set and display the configuration options on your server. You can do the same thing with the Properties panel of the Server object. Any time you make a change there, this report will track that.

The second mechanism is where things get interesting. By default, SQL Server records certain events about your system in something called a “Default Trace”. It’s an engine that watches SQL Server and records events to a file. I’ll cover this in more detail tomorrow, but you should know two things about the trace: It doesn’t record every event on the Instance, and it can be turned off. That second part is important, because if it is turned off, you won’t have much information in this report or the one I’ll show you tomorrow. Here’s how you can find out if it is turned on:

Open a query window and run this script:

USE master;

EXEC sp_configure 'show advanced option', '1';

RECONFIGURE WITH OVERRIDE;

EXEC sp_configure;

GO

Look for the default trace enabled value and check to see that the run value is 1. If it is 0, find out who turned it off and why. It’s a small, circular file so for the most part it should be turned on.

I’ll talk a lot more about the Default Trace tomorrow.

To find this report, open SSMS, right-click a Server, and then select “Reports” and then “Standard Reports”.  This report doesn’t take a long time to render.

Heading

Description

Configuration Option

Shows the option that was changed.

Old Value

Shows the value of the option before it was changed.

New Value

Shows the value of the option before it was change.

Time

Shows the date and time when the option was changed

User

Shows the account that was used to make the change.  This can be less useful if you allow multiple people to use the same administrative account, so it’s a best practice for each administrator to have their own account.

 

Leave a Comment
  • Please add 8 and 4 and type the answer here:
  • Post
Page 1 of 1 (3 items)