Cannot disable a server audit on Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) .
When I try to disable the server audit from either SSMS or command "ALTER SERVER AUDIT [Audit-Billing] WITH (STATE = OFF)", it runs for hours. When I tried to kill the SPID, it got stuck in 'KILLED/ROLLBACK' state, and I got the following message: SPID 273: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds. Here are the results from sys.dm_exec_requests:
session_id start_time status command wait_type Last_wait_type
273 2011-10-24 11:40:16.380 running KILLED/ROLLBACK PREEMPTIVE_XE_SESSIONCOMMIT PREEMPTIVE_XE_CALLBACKEXECUTE
Here are the results from sys.dm_tran_locks:
resource_description resource_type resource_subtype resource_database_id resource_lock_partition request_mode
audit_id = 65537 METADATA AUDIT 1 22 Sch-M
Hope to get some advice ASAP because this is on our production server. Thanks in advance!