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')
BEGIN
	DROP TRIGGER ddl_trig_alterdatabasescopedconfig
	ON ALL SERVER;
END	
GO
CREATE TRIGGER ddl_trig_alterdatabasescopedconfig
ON ALL SERVER 
FOR ALTER_DATABASE_SCOPED_CONFIGURATION
AS
BEGIN
	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;
END
GO

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!

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.