Blog Post

Get Configuration Change History From the SQL Server Error Logs

,

Get Configuration Change History From the SQL Server Error Logs

There are several options if and when you need to see if any configuration change was made in SQL Server.

The simplest maybe to use the SSMS GUI, using its built-in (aka standard) reports:

 

The good thing about this standard report is that it reads from the default trace, which being the "default" setting, would have been already running, that means you don't need to setup and enable it. It is just there, unless of course it was purposely disabled/stopped.

The bad is that the default trace is of limited size, I think the size limit is 20 MB with up to 5 rollover files. You cannot change the size of default trace, you can however create your own trace, in which case better to look at setting up Extended Events instead.

You can also use the SQL Server Auditing and Extended Events features as well, which gives you lot more control on what to capture and to what size. The downside is that you need to set it up and it should be already running by the time there is a need to check if any configuration change was made.

SQL Server also logs anytime there is a configuration change, which you can then view and filter to find the configuration changes by searching for string "Configuration option". You can obviously use the SSMS to open and filter the log file, or even your favorite text editor. And better, you can programmatically read and filter using the sys.xp_readerrorlog:

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-readerrorlog-transact-sql


Here are some examples:

Example 1: Search for entries containing Configuration option

USE master;
EXEC sys.xp_readerrorlog
0, -- 0 for the current log file, 1 = archive file #1 etc
        1, -- 1 or NULL for SQL Server error log, 2 for the SQL Agent log
        N'Configuration option', -- Search/filter by this string
        NULL, -- Further refine the search/filter condition
        NULL, -- Start time
NULL, -- End time
N'desc'; -- Sorts the results by asc for ascending order or 
                 -- desc for descending order

Example 2: A second filter for the Start Time
USE master;
EXEC sys.xp_readerrorlog
0, -- 0 for the current log file, 1 = archive file #1 etc
        1, -- 1 or NULL for SQL Server error log, 2 for the SQL Agent log
        N'Configuration option', -- Search/filter by this string
        NULL, -- Further refine the search/filter condition
        '2023-09-08 16:00:00', -- Start time
NULL, -- End time
N'desc'; -- Sorts the results by asc for ascending order or 
                 -- desc for descending order

Example 3: Further refine/search into the results
USE master;
EXEC sys.xp_readerrorlog
0, -- 0 for the current log file, 1 = archive file #1 etc
        1, -- 1 or NULL for SQL Server error log, 2 for the SQL Agent log
        N'Configuration option', -- Search/filter by this string
        N'Optimize', -- Further refine the search/filter condition
        NULL, -- Start time
NULL, -- End time N'desc'; -- Sorts the results by asc for ascending order or
                 -- desc for descending order

So, what is the catch here? Every time SQL Server is restarted, it starts a new log file and renames the previous log file to errorlog.1, and the previous errrolog.1 gets renamed to errorlog.2 and so forth, up to 7 logs, which you can increase up to 99. Stored procedure sp_cycle_errorlog can be used to start a new log file in between the SQL Server restarts.  That means 1) The older configuration change entries would get moved to the so called archived error logs, which sys.xp_readerrorlog does allow to read from as well, and 2) Eventually the oldest archived error log files will get removed, unless of course you setup something to store the log entries somewhere.

Of course, you can also use sys.xp_readerrorlog for other purposes as well, for example to see if there have been any errors in last 24 hours.

USE master;
declare @start_time datetime = getdate() - 1
EXEC sys.xp_readerrorlog
    0, -- 0 for the current log file, 1 = archive file #1 etc
    1, -- 1 or NULL for SQL Server error log, 2 for the SQL Agent log
    N'Severity: 16', -- Search/filter by this string
    N'41145', --  Further refine the search/filter condition
    @start_time, -- Start time
    NULL, -- End time
    N'desc'; -- Sorts the results by asc for ascending order 
             -- or desc for descending order


Original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating