0

I am trying to delete server audit which was capturing logon events defined in server audit specification as follows:

CREATE SERVER AUDIT SPECIFICATION [System Login Audit]
FOR SERVER AUDIT [System Logins]
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (DBCC_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP)
WITH (STATE = ON)

I managed to delete mentioned above server audit specification but still can not delete related server audit. My attempt to disable it with command
ALTER SERVER AUDIT [System Logins] WITH (STATE = OFF) ends up being blocked by something and won't let me delete it.

My most radical attempt was to execute this command while running SQL Server service in single-user mode with SQL Server Agent and SQL Server CEIP service both being disabled.

Alex Zhyk
  • 11
  • 3
  • 1
    `blocked by something` -> you know something is blocking it? How? If you know it's "something" you should know exactly? What does `sys.dm_exec_requests` say while the `ALTER` command is running? Is there a value in `blocking_session_id`? What is _that_ session doing/ – Stuck at 1337 Aug 29 '23 at 13:49
  • I don't know what is blocking it. Execution of the command just takes forever. There is nothing like blocking session_id. `sys.dm_os_waiting_tasks` shows `XE_BUFFERMGR_ALLPROCESSED_EVENT` and `sp_lock` gives `MD 57(10005:0:0) Sch-M` – Alex Zhyk Aug 29 '23 at 14:05
  • Description for that wait is `Occurs when Extended Events session buffers are flushed to targets. This wait occurs on a background thread.` are you sure you are looking at the right process? How is XEvents relevant, do you have an XEvents trace set up? I'm wondering if there is something preventing the Audit from being flushed, does the disk have enough space? – Charlieface Aug 29 '23 at 14:53
  • I should say, that this is production server and at this point I must have tried to kill that process. So, currently its' status is `suspended` and command is `KILLED/ROLLBACK`. That, however, makes request with that session_id being blocking for many consequent requests to query server metadata. What I mean by this, is executing, for example `select * from sys.server_audits` will result also in response "Executing query..." in SSMS which will be endless and `sys.dm_exec_requests` will show that is is blocked by session which I am trying to fix here. – Alex Zhyk Aug 29 '23 at 15:10
  • I wish I can reproduce tabular results in comment, but querying `sys.dm_tran_locks` for that statement shows what can be interpreted as "transaction to delete server audit is blocked by server audit which is capturing changes to server metadata" Here is the screenshot of the result: [link](https://ibb.co/drY79Ct) – Alex Zhyk Aug 29 '23 at 15:47

1 Answers1

1

Ok. As I wrote in the comments, I was trying to delete the server audit which was blocked by that very server audit which was capturing changes to server metadata what can be illustrated by running the query: select * from sys.dm_tran_locks where request_session_id = <that_session_id> see sample output of the query

Metaphorically speaking this was like cutting tree branch while sitting on it.

Apparently, running server in just single-user mode, which I tried earlier was not enough.

The solution was to start SQL Server in minimal configuration (-f) with monitoring features disabled (-x)

net start "SQL Server (MSSQLSERVER)" /x /f /mSQLCMD and then to disable audit by running statement in SQLCMD: ALTER SERVER AUDIT [System Logins] WITH (STATE = OFF)

Alex Zhyk
  • 11
  • 3