Post reply

Need a script to audit the DDL & DML events for SQL server 2000

  • February 25, 2009 at 12:25 am

    #206925

    Hi,

    I want to generate a report at the end of the month to see what all activities have been performed on my databases. I am looking for a script which will help me generate this report. The report should contain details like operation performed, date, time, username,database name , etc.

    I checked it with Profiler but since it is an overhead on performance, thought of checking for a script. This can be done by Triggers but I have not used it earlier. Can someone help me out?

    Thanks in Advance

    ----------------------------------------------------------------
    **"There is only one difference between dream and aim. Dream requires Soundless sleep to see…Where as Aim Requires Sleepless Efforts to Achieve":-) **

  • February 25, 2009 at 8:56 pm

    #950384

    I haven't received any reply yet. Can someone help me out urgently?

    Thanks.

    ----------------------------------------------------------------
    **"There is only one difference between dream and aim. Dream requires Soundless sleep to see…Where as Aim Requires Sleepless Efforts to Achieve":-) **

  • February 26, 2009 at 1:21 am

    #950449

    This is a forum, not a chat room. Be patient.

    On SQL 2000, the only way to get all DDL and DML statements is to use a trace. Not profiler, too much overhead, use a server-side trace.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 26, 2009 at 3:56 am

    #950495

    Are you refering to Perfmon? If not then please provide me the details on server side trace.

    ----------------------------------------------------------------
    **"There is only one difference between dream and aim. Dream requires Soundless sleep to see…Where as Aim Requires Sleepless Efforts to Achieve":-) **

  • February 26, 2009 at 9:09 am

    #950661

    sqlserverdba.dba (2/26/2009)

    Are you refering to Perfmon?

    No. Perfmon is a windows tool. I'm referring to SQL Profiler and the stored procs that it uses.

    If not then please provide me the details on server side trace.

    Check books online, the section titled "Creating and Managing Traces and Templates" Specifically towards the bottom where the various stored procs are described.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 27, 2009 at 12:43 am

    #951008

    Thanks. But i need to run it on on-going basis. Will this not cause an overhead on performance if I run daily?

    Thanks

    ----------------------------------------------------------------
    **"There is only one difference between dream and aim. Dream requires Soundless sleep to see…Where as Aim Requires Sleepless Efforts to Achieve":-) **

  • February 27, 2009 at 1:52 am

    #951023

    sqlserverdba.dba (2/27/2009)

    Thanks. But i need to run it on on-going basis. Will this not cause an overhead on performance if I run daily?

    Not if you do it properly. Server-side trace to a fast disk that is not used for any database files, a minimal number of events, a minimal number of columns.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 7 posts - 1 through 6 (of 6 total)

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