Post reply

Viewing SQL Trace Output

  • February 19, 2004 at 6:38 am

    #82003

    I'm looking into enabling C2 auditing.  I will need to provide the audit file to managers in a readable format. It appears that trc files are specific to sql profiler.  What is the best way to provide c2 auditing results to users in a report or file format?

    thanks

     

    EDIT: I meant SQL PROFILER not SQL TRACE...

  • February 19, 2004 at 9:13 am

    #494732

    Save the trace file into a table and let your manager to query the information from there.

  • February 20, 2004 at 1:39 am

    #494874

    can this be automated somehow? i don't want to have to manually do this every day to update the table.

  • February 20, 2004 at 10:50 am

    #494979

    Write script to start trace and stop it, save the result into a table.  Put this script into job ans schedule to run on daily basis.  You can then:

    -- Use sp_makewebtask stored procedure to read the result table and save it on html file. (see sp_makewebtask on BOL)

    --use .xp_smtp_sendmail extended stored procedure to send the html file as an email message.

    This is what I am using to receive reports on daily basis.  I think this is a great way to be notified.

  • February 22, 2004 at 8:47 pm

    #495079

    You can use ::fn_trace_gettable to read trace files and/or load them to SQL tables.  Other undocumented extended stored procedures (eg. xp_dirtree and xp_getfiledetails) can be incorporated to determine the files generated by C2 auditing.

    Care should be taken because you may be loading up to 200mb with a call to ::fn_trace_gettable.

    While automating things, make sure you automate cleanup (or at least something to send you warnings) of the huge data volumes that can be generated by C2.

     

     


    Cheers,
    - Mark

Viewing 5 posts - 1 through 4 (of 4 total)

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