Logging Database Scoped Configuration Changes

The introduction of DATABASE SCOPED CONFIGURATIONS in SQL Server 2016 enabled different configuration settings at the individual database level. However, there is no logging of changes to the database scoped settings by default in SQL Server, making it nearly impossible to track down when a change was made and by who. After recently working on a client problem where performance issues were attributed to a DATABASE SCOPED CONFIGURATION of MAXDOP = 1 multiple times, I decided to create a DDL trigger for the ALTER_DATABASE_SCOPED_CONFIGURATION events in SQL Server to have it log the change to the ERRORLOG file similar to the one I wrote years ago for logging Extended Event session changes.

IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = N'ddl_trig_alterdatabasescopedconfig')
	DROP TRIGGER ddl_trig_alterdatabasescopedconfig
CREATE TRIGGER ddl_trig_alterdatabasescopedconfig
	DECLARE @CommandText NVARCHAR(MAX) = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)');
	DECLARE @LoginName SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(128)');
	DECLARE @PostTime NVARCHAR(MAX) = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','nvarchar(128)');
	DECLARE @DatabaseName NVARCHAR(MAX) = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(128)');

	DECLARE @ErrorMsg NVARCHAR(MAX) = '''%s'' was executed on ''%s'' by Login: ''%s'' at ''%s''.';

	RAISERROR(@ErrorMsg, 10, 1, @CommandText, @DatabaseName, @LoginName, @PostTime) WITH LOG;

At least with this logged there is a means of going back and searching for what changed and when without having to think about all the different features and functionality that could impact performance. Hope it helps!

2 thoughts on “Logging Database Scoped Configuration Changes

  1. Very nice, I went ahead and implemented this on my production SQL Servers, since other sysadmins could make these types of changes.

    This is only useful if you review the SQL error logs before they rotate out. I use a tool to aggregate and parse my production SQL errors logs for unusual entries such as this one. Reviewing these entries is part of a “Daily Check” that I do at the beginning of each day.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.