Unable to disable Server Audit on SQL Server 2008 R2 SP1 - by DBA_MMM

Status : 

  External<br /><br />
		This item may be valid but belongs to an external system out of the direct control of this product team.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


3
0
Sign in
to vote
ID 696484 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 10/24/2011 3:26:36 PM
Access Restriction Public

Description

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!
Sign in to post a comment.
Posted by Vithor da Silva e Silva on 6/13/2014 at 6:15 AM
The same problem in this version: Microsoft SQL Server 2008 (SP2) - 10.0.4067.0 (X64) Jun 14 2012 19:26:05 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Posted by Microsoft on 10/28/2011 at 10:02 AM
Hi Elena,

There could be multiple things involved and we cannot say for sure unless we get a trace of what's going on on the server.
The best bet would be to systematically work with CSS so the proper root cause of the issue is understood and addressed.

Thank you and regards,
Don Pinto
SQL Server Engine
Posted by DBA_MMM on 10/28/2011 at 7:10 AM
Hi Don,

I open a case with Microsoft on Wednesday. Meanwhile, the session still hangs although it is not blocking anything, but at some point this needs to be resolved. Could this be related to http://connect.microsoft.com/SQLServer/feedback/details/383878/extended-event-session-hangs in any way? Please advise. Thanks!
Elena
Posted by Don Pinto - MSFT on 10/27/2011 at 11:14 AM
Hi,

First of all, thank you for your feedback !

Since this is an issue affecting your production server, I would suggest to start with opening a case with Microsoft CSS (https://support.microsoft.com/oas/default.aspx?gprid=1044&st=1&wfxredirect=1&sd=gn) to investigate this further.

Thank you and regards,
Don Pinto
SQL Server Engine