Tracking sp_configure Changes Through Error Log Messages

Tracking sp_configure changes starts with the SQL Server error log. Every change leaves a short message there, with the option name and its old and new values. It will not tell you who made the change, so know what you can and cannot prove.

The question after the slowdown

Monday morning, the server feels different. Queries that were fine on Friday are crawling. Someone asks, “Did anybody change a setting over the weekend?” Nobody admits to anything. That is when you open the error log.

Let me change a setting on purpose, so you can see what it leaves behind. This demo changes a server-level option, nested triggers, and puts it back by the end of the third block. Use a test server, not production.

Look at the setting before you touch it

The sys.configurations view has two value columns. The value column is what is configured. The value_in_use column is what SQL Server is actually running right now. Normally they match.

SELECT name, value AS configured_value, value_in_use AS running_value, is_dynamic
FROM sys.configurations
WHERE name = N'nested triggers';

Both values are 1, and is_dynamic is 1, so a change takes effect without a restart.

Change it, and see what you get back

EXEC sys.sp_configure N'nested triggers', 0;
RECONFIGURE;

SELECT name, value AS configured_value, value_in_use AS running_value
FROM sys.configurations
WHERE name = N'nested triggers';

SQL Server answers with a message: the option changed from 1 to 0, and you should run RECONFIGURE to install it. We did run it, so both columns show 0. That message is the same text that lands in the error log.

The in-between state

Here is a trap. Run sp_configure without RECONFIGURE, and the two columns disagree. The setting is saved, but nothing is running with it yet.

EXEC sys.sp_configure N'nested triggers', 1;

SELECT name, value AS configured_value, value_in_use AS running_value
FROM sys.configurations
WHERE name = N'nested triggers';

RECONFIGURE;

The configured value is 1, but the running value is still 0. Whenever you find a gap like this on a real server, someone ran sp_configure and never finished the job. The RECONFIGURE at the end of this block closes the gap and puts nested triggers back to its original value of 1.

Read the error log

Now ask the error log what it saw. The procedure xp_readerrorlog takes the log number (0 is the current log), the log type (1 is SQL Server), and up to two search words. I keep the messages in a temp table, so the original text stays untouched. The LogId column keeps the messages in the order SQL Server wrote them.

CREATE TABLE #ConfigLog
(
    LogId       int IDENTITY(1,1),
    LogDate     datetime,
    ProcessInfo nvarchar(64),
    MessageText nvarchar(max)
);

INSERT #ConfigLog (LogDate, ProcessInfo, MessageText)
EXEC master.dbo.xp_readerrorlog 0, 1, N'Configuration option', N'nested triggers';

SELECT LogDate, ProcessInfo, MessageText
FROM #ConfigLog
ORDER BY LogId;

DROP TABLE #ConfigLog;

You get one line per sp_configure call: a change from 1 to 0, then one from 0 to 1, each with the same wording as the message you saw earlier. Every time you run the demo, two more lines appear. Notice what is missing: no login name, no application, no reason. At best, the ProcessInfo column gives a session number, and that session is long gone.

What to do with this

Search the older logs too. Change the first number to 1, 2 and so on to read archived files. Recycling the log or restarting the service can push old messages out, so save what you find.

Reading the log needs elevated permission. Test with the account that will collect it, and do not hand out broad access just for one report.

To answer “who”, plan ahead. Set up an audit or an Extended Events session for configuration changes before you need it. A person who logged in nearby is not evidence of anything.

Next time a setting looks off, read the log and the two value columns before you blame anyone.

The error log is not an audit trail, it is a note that a setting changed.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.