snippetsqlMinor
How can I detect what's changing my scoped configurations?
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
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
Now, for each database:
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:
My results after one change to
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);
GONow, for each database:
USE database_name;
GO
CREATE DATABASE AUDIT SPECIFICATION CatchDBConfigChangers
FOR SERVER AUDIT ServerAudit
ADD (DATABASE_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
GOYou'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);
GOUSE database_name;
GO
CREATE DATABASE AUDIT SPECIFICATION CatchDBConfigChangers
FOR SERVER AUDIT ServerAudit
ADD (DATABASE_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);
GOSELECT
[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.