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)','nvarchar(max)'); DECLARE @LoginName SYSNAME = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)','nvarchar(128)'); DECLARE @PostTime NVARCHAR(MAX) = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)','nvarchar(128)'); DECLARE @DatabaseName NVARCHAR(MAX) = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)','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!