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 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.
You probably already know that you can’t make changes directly to sys.configurations. If you try you’ll get the error:
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):
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:
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.