HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How can I detect what's changing my scoped configurations?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
canwhatconfigurationsscopedhowchangingdetect

Problem

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

Solution

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):

Code Snippets

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
USE database_name;
GO

CREATE DATABASE AUDIT SPECIFICATION CatchDBConfigChangers
  FOR SERVER AUDIT ServerAudit
  ADD (DATABASE_OBJECT_CHANGE_GROUP)
  WITH (STATE = ON);
GO
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'
;

Context

StackExchange Database Administrators Q#244700, answer score: 5

Revisions (0)

No revisions yet.