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!
2 thoughts on “Logging Database Scoped Configuration Changes”
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.