Home Dashboard Directory Help

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


 as External Help for as External

Sign in
to vote
Type: Bug
ID: 696484
Opened: 10/24/2011 3:26:36 PM
Access Restriction: Public
User(s) can reproduce this bug


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
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!
Posted by Don Pinto - MSFT on 10/27/2011 at 11:14 AM

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
Sign in to post a workaround.