Logging changes to sys.configurations

Some server wide settings can have major negative impact if they are changed in your SQL Server environment. So as a DBA it is a good practice to monitor changes to these setting, and to raise alerts when they are changed. To do this, the system view sys.configurations can be monitored for changes.

This blog post will demonstrate a way to monitor SQL Server configuration settings. The solution will also give you get the complete history for the different settings in sys.configurations.

Note that database mail needs to be configured for the current solution to work properly.

sys.configurations

Sys.configurations has about 70 settings (69 on my laptop running SQL Server 2012 Developer Edition). Here is a sample of the content:

Some of these settings can have a major effect on your system if they are changed, so keeping track of them is a good idea.

More information on the table sys.configurations can be found on TechNet. More information on the SQL server configuration options can be found here.

You probably already know that you can’t make changes directly to sys.configurations. If you try you’ll get the error:

[red_box]Msg 259, Level 16, State 1, Line 1 Ad hoc updates to system catalogs are not allowed.[/red_box]

To make configuration changes, use the system stored procedure sp_configure instead.

Stored procedure to monitor changes in sys.configurations: GetSysConfigurationChanges

Part of the solution is a table to store the settings of sys.configurations. Whenever a change in sys.configurations is detected, a new row with the new value will be inserted to this table. Please use a database on your own choice.

CREATE TABLE dbo.tblSysConfigurationChanges
    (
        SysConfigurationChangeId INT IDENTITY(1,1) NOT NULL CONSTRAINT SysConfigurationChangeId_PK PRIMARY KEY CLUSTERED
        ,IsCurrentValue BIT DEFAULT(1) NOT NULL
        ,SnapshotTime DATETIME2 DEFAULT(GETDATE()) NOT NULL
        ,configuration_id INT NOT NULL
        ,[value] BIGINT NOT NULL
        ,value_in_use BIGINT NOT NULL
        ,old_value BIGINT NULL
        ,old_value_in_use BIGINT NULL
    )
GO

CREATE UNIQUE INDEX
    tblSysConfigurationChanges_IDX
ON
    dbo.tblSysConfigurationChanges (configuration_id,[value],value_in_use)
WHERE
    IsCurrentValue = 1
GO

SysConfigurationChangeId is simply the primary key. IsCurrentValue is used to show which reading from sys.configurations is the latest. The latest measurement is used to compare to the current settings to know if the value has been changed. SnapshotTime shows when the value was read from sys.configurations. configuration_id is the value that is used as key to sys.configurations. value and value_in_use are the columns that are monitored for changes. The same columns with the prefix old_* are there to make it easier to see the previous value.

A stored procedure is used to monitor for changed values in sys.configurations:

CREATE PROCEDURE dbo.GetSysConfigurationChanges
	(
		@EmailProfile SYSNAME
		,@EmailRecipients VARCHAR(MAX)
	)
 
AS
 
SET NOCOUNT ON

DECLARE
	@TotalRows INT
	,@CurrentRow INT = 1
	,@ConfChangeMessage NVARCHAR(MAX)
	,@MailSubject NVARCHAR(255)

DECLARE @TBL_CHANGED_VALUES TABLE
	(
		RowNo INT IDENTITY(1,1) NOT NULL
		,SysConfigurationChangeId INT NOT NULL
		,configuration_id INT NOT NULL
        ,[value] BIGINT NOT NULL
        ,value_in_use BIGINT NOT NULL
        ,old_value BIGINT NULL
        ,old_value_in_use BIGINT NULL
	)

INSERT dbo.tblSysConfigurationChanges
    (
        configuration_id
        ,[value]
		,value_in_use
		,old_value
		,old_value_in_use
    )
OUTPUT
    INSERTED.SysConfigurationChangeId
    ,INSERTED.configuration_id
    ,INSERTED.[value]
    ,INSERTED.value_in_use
    ,INSERTED.old_value
    ,INSERTED.old_value_in_use
INTO
    @TBL_CHANGED_VALUES
SELECT
    sc.configuration_id
    ,CAST(sc.[value] AS BIGINT)
    ,CAST(sc.value_in_use AS BIGINT)
    ,CAST(scc.[value] AS BIGINT)
    ,CAST(scc.value_in_use AS BIGINT)
FROM
    sys.configurations sc
LEFT OUTER JOIN
    dbo.tblSysConfigurationChanges scc
ON
    sc.configuration_id = scc.configuration_id
AND
    scc.IsCurrentValue = 1
WHERE
    (
        sc.[value] <> ISNULL(scc.[value], CAST(sc.[value] AS BIGINT) + 1)
    OR
        sc.value_in_use <> ISNULL(scc.value_in_use, CAST(sc.value_in_use AS BIGINT) + 1)
    )
 
UPDATE
    scc
SET
    IsCurrentValue = 0
FROM
    @TBL_CHANGED_VALUES T0
JOIN
    dbo.tblSysConfigurationChanges scc
ON
    T0.configuration_id = scc.configuration_id
AND
    T0.SysConfigurationChangeId > scc.SysConfigurationChangeId

IF EXISTS (SELECT * FROM @TBL_CHANGED_VALUES) BEGIN
	SELECT @TotalRows = COUNT(*) FROM @TBL_CHANGED_VALUES
	SELECT @MailSubject = 'Changes in server configurations detected! On server ' + @@SERVERNAME
	SELECT @ConfChangeMessage = 'The following server configuration settings have been changed: ' + CHAR(13)

	WHILE @CurrentRow <= @TotalRows BEGIN

		SELECT
			@ConfChangeMessage = @ConfChangeMessage + CHAR(13) + CHAR(13) + sc.[name]
			+ ' was changed to VALUE ' + CAST(tcv.value AS VARCHAR(20)) + ' from ' + CAST(ISNULL(tcv.old_value, '') AS VARCHAR(20))
			+ ', and VALUE IN USE to ' + CAST(tcv.value_in_use AS VARCHAR(20)) + ' from ' + CAST(ISNULL(tcv.old_value_in_use, '') AS VARCHAR(20))
		FROM
			@TBL_CHANGED_VALUES tcv
		JOIN
			sys.configurations sc
		ON
			TCV.configuration_id = SC.configuration_id
		WHERE
			RowNo = @CurrentRow
		SELECT @CurrentRow = @CurrentRow + 1
	END

	EXEC msdb.dbo.sp_send_dbmail
		@profile_name = @EmailProfile
		,@recipients = @EmailRecipients
		,@subject = @MailSubject
		,@body = @ConfChangeMessage
END

GO

As soon as changed values in sys.configurations are detected, the new values will be stored in dbo.tblSysConfigurationChanges, and an email will be sent with a notification of the event. The procedure has two input parameters. Submit your SQL Server Mail Profile to the @EmailProfile parameter, and a comma separated list of email recipients in the @EmailRecipients parameter.

The first time the procedure is run, it will list all settings as changed. This is because there are no old values to compare to. The first email can therefore be ignored.

I suggest you use SQL Agent and schedule this procedure to run at a regular interval. Perhaps once every hour, or more often if you need the alerts sooner. Just remember that changes between scheduled runs aren’t detected.

The email looks like this sample:

Note that some of the settings aren’t changed until a SQL Server Service restart. “VALUE IN USE” in the email above means a running (actually used) value in SQL Server. “VALUE” means a configured value that will be used after the next service restart. Fill factor is an example of configuration setting that won’t change until a service restart. If you change the fill factor value with the following SQL (don’t run this in a production environment):

EXEC sys.sp_configure N'fill factor (%)', N'75'
GO
RECONFIGURE WITH OVERRIDE
GO

And run the GetSysConfigurationChanges procedure, the email message body may be (depending on your settings):

[white_box]fill factor (%) was changed to VALUE 75 from 70, and VALUE IN USE to 70 from 70[/white_box]

This means that the fill factor will be changed to 75 when the service is restarted and that the value in use still is 70 until the restart. So after a restart you will again be notified of the change, but this time the value in use will have changed:

[white_box]fill factor (%) was changed to VALUE 75 from 75, and VALUE IN USE to 75 from 70[/white_box]

So if you get an email with a lot of changed settings after a restart, this is because of pending changes.

To view to complete history for a configuration setting, use the following t-sql template:

SELECT * FROM dbo.tblSysConfigurationChanges WHERE configuration_id = 109

In this sample, I started out with a fill factor of 0 (same as 100). I later changed it to 85, but the value in use vas still 0. Lastly, I restarted the SQL Server service, and the value in use changed to 85.