Post reply

Turn of a Sql server audit trace

  • March 21, 2014 at 7:29 am

    #305565

    Somehow someone turned on a audit on the sql server and it is filling up our hard drive and shutting down sql server eventually. Been trying to google how to shut this audit off but coming up with no via soolution yet. how can I turn this trace off. Each fiel says AuditTrace and date and they happen every other minute. I went into the sql profiler and can pull up the files but how to shut the trace off, it does not say.

  • March 21, 2014 at 7:34 am

    #1699083

    Run

    SELECT * FROM sys.traces

    to get the trace id (in the first column).

    Then, assuming that in this example your trace id is 2 (- it might not be!) stop the trace

    exec sp_trace_setstatus @TraceID = 2, @status = 0

    (Then if you want to delete the trace entirely, run this. You might want to leave it for now.)

    exec sp_trace_setstatus @TraceID = 2, @status = 2

  • March 21, 2014 at 7:52 am

    #1699093

    HI, ok it says I do not have permissions to run the procedure. I logged intot he sql server as an admin and then ran SSMS as an admin, as its still squacking at me. any ideas? Ty for the help

  • March 21, 2014 at 9:08 am

    #1699116

    Under Security > Logins > [your login], what does it have under Server Roles?

  • March 21, 2014 at 9:08 am

    #1699117

    Sorry; duplicate again.

  • March 21, 2014 at 9:43 am

    #1699143

    HI,

    I have public and Sysadmin checked,

  • March 21, 2014 at 9:45 am

    #1699144

    Huh. Could you post the exact error message please?

  • March 21, 2014 at 9:45 am

    #1699145

    ok,

    decided to check everything and then logged out of SSMS, and logged back in and it gave me the same error.

  • March 21, 2014 at 9:48 am

    #1699147

    Did it allow you to run this bit?

    SELECT * FROM sys.traces

  • March 21, 2014 at 9:52 am

    #1699153

    Hi,

    yes, I can see the trace, there is only one going, reading something about a defaut trace that runs on sql server, but whomever set up this server changed the trace to the data drive which is filling it up and locking up sql server after about a month. If I cannot shut down the default trace can I move it and limit it to so many files before it deletes

  • March 21, 2014 at 9:57 am

    #1699160

    No, don't disable that one! Is it writing to the location that is filling up?

    Is it definitely a trace? Are there any audits running (under the Security menu)?

  • March 21, 2014 at 10:26 am

    #1699182

    Stubby Bunny (3/21/2014)

    Hi,

    yes, I can see the trace, there is only one going, reading something about a defaut trace that runs on sql server, but whomever set up this server changed the trace to the data drive which is filling it up and locking up sql server after about a month. If I cannot shut down the default trace can I move it and limit it to so many files before it deletes

    EXACTLY how many traces are returned by select * from sys.traces ? do any of them have a PATH column that are not null? those are profiler style traces, that are directing their output to an application.

    the default trace is Usually ID = 1.

    the default trace cannot fill up your drive.

    select * from sys.traces where is_default = 1

    if the ID is 2, then someone has enabled the C2 Audit, which certainly can fill up your drive.

    if there are any other traces, other than the default trace, THOSE are what you want to disable.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply