Post reply

SQL Profiler vs Sys stored procedures

  • September 14, 2010 at 9:44 am

    #103984

    Hi DBA's

    I am trying to run a trace on SQL server 2000 named instance using System Sp's (Sp_create_trace, sp_add_event etc ..) the file is getting generated but its empty for ever, for some reason it is not writing anything to the file. If I run the profiler it is working jest fine. Please help, Thanks in advance

    Thanks

    Raghu

  • September 14, 2010 at 2:03 pm

    #1220953

    Easy way out is script to script the profiler trace and run it on the instance. You may find this[/url] useful.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • September 14, 2010 at 2:42 pm

    #1221001

    I agree with Pradeep, script it out from Profiler, and then see where you might be going wrong.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • September 24, 2010 at 1:38 pm

    #1227206

    Hi All

    Sorry for a late response, i tired scripting out and run the same in management studio. Didn't work. any thoughts?

  • September 24, 2010 at 1:56 pm

    #1227215

    What is the error? The trace does not start at all?

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • September 24, 2010 at 6:47 pm

    #1227303

    No errors the status is running and file gets created but doesn't write anything to it. Trc file stays empty and 0 kb

    Raghu

  • September 25, 2010 at 2:42 am

    #1227345

    The size will not reflect correctly until the trace file is rolled over or the trace is stopped.

    You may try stopping and closing the trace file to see if it is actually capturing information.

    exec sp_trace_setstatus TraceIDHere, 0 --stop

    exec sp_trace_setstatus TraceIDHere, 2 --close

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • September 27, 2010 at 6:50 pm

    #1228115

    Hi Pradeep

    As soon as I stop the trace, the file size grows to 128KB and the content of the trace file will be.. "The trace has been stopped". While the trace is running I have done lot of activity (create, Drop, alter, deny, revoke) but nothing was written. But as I mentioned earlier it captures everything when I run the profiler.

    Thanks

    Raghu

  • September 27, 2010 at 9:04 pm

    #1228125

    dbadotraghu (9/27/2010)

    Hi Pradeep

    As soon as I stop the trace, the file size grows to 128KB and the content of the trace file will be.. "The trace has been stopped". While the trace is running I have done lot of activity (create, Drop, alter, deny, revoke) but nothing was written. But as I mentioned earlier it captures everything when I run the profiler.

    If the trace definition has the events to be captured, those events will be captured in the server side trace. Double check the events that needs to be traced and include them in the script

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • September 27, 2010 at 9:52 pm

    #1228129

    Whenever server face Memory crunch or performance issue then profiler skips some events because they require more resources to trace the event. If you want to capture all the events, you need server side tracing. This may help you.

    Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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