SQL Server – How to Detect Changes in Scoped Configurations

sql serversql-server-2016

I'm noticing that I have a database that keeps resetting the scoped configuration , namely maxdop.

Is there any logs that show who or what process is causing these configuration changes?
I'm on Microsoft SQL Server 2016 (SP1-CU7-GDR) (KB4057119) – 13.0.4466.4

Best Answer

Well, you won't have any history, but you can catch this in the act in the future, since SQL Server Audit can do this, using DATABASE_OBJECT_CHANGE_GROUP:

USE master;
GO

CREATE SERVER AUDIT ServerAudit
  TO FILE (FILEPATH = 'C:\path\to\audit\folder\', MAXSIZE = 1 GB)
  WITH (ON_FAILURE = CONTINUE);
GO

ALTER SERVER AUDIT ServerAudit
  WITH (STATE = ON);
GO

Now, for each database:

USE database_name;
GO

CREATE DATABASE AUDIT SPECIFICATION CatchDBConfigChangers
  FOR SERVER AUDIT ServerAudit
  ADD (DATABASE_OBJECT_CHANGE_GROUP)
  WITH (STATE = ON);
GO

You'll have to create an audit specification per database, and this will collect more than just scoped configuration changes, but once you have set up your databases, you can start checking for details immediately:

SELECT 
  [when]      = event_time,
  [app]       = application_name, 
  [host]      = host_name, 
  [ip]        = client_ip,
  [login]     = server_principal_name,
  [statement] 
FROM sys.fn_get_audit_file 
  ('C:\path\to\audit\folder\ServerAudit*', NULL, NULL)
WHERE LOWER([statement]) LIKE N'%scoped%configuration%'
-- AND database_name = N'database_name'
;

My results after one change to MAXDOP (click to enlarge):

enter image description here

Related Question