Post reply

Trace not picking SQL statements

  • February 10, 2012 at 9:00 am

    #250609

    I ran a trace through Profiler, the SQL statements are showing up under Textdata column for all the system databases but not for the only user database that exists on the server.

    I'm getting the following lines under the textdata column for the user database.

    -- network protocol: Named Pipes set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transact

  • February 10, 2012 at 9:07 am

    #1445640

    What events do you have selected and do you have any filters in place aside from the default?

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • February 10, 2012 at 9:21 am

    #1445647

    I've the following events and no filters

    SQL:BatchStarting

    SQL:BatchCompleted

    Audit Schema Object Access Event

    Existing Connection

    RPC completed

    Audit Login

    Audit Logout

  • February 10, 2012 at 9:49 am

    #1445671

    sunny.tjk (2/10/2012)

    I've the following events and no filters

    SQL:BatchStarting

    SQL:BatchCompleted

    Audit Schema Object Access Event

    Existing Connection

    RPC completed

    Audit Login

    Audit Logout

    Unless you are looking for connection information to be collected I would remove the Audit Login / Logout and Existing Connection. Not sure why you have Audit Schema... selected.

    I would have the following to be sure that I was able to catch what you want;

    SP:Completed

    SP:StmtCompleted

    SQL:BatchCompleted

    SQL:BatchStarting

    SQL:StmtCompleted

    You can use the "Starting" version for those as well if wanted either in addition to or in place of the "Completed".

    Play around with that and see what you get. One note here but if you have a really busy server and you run a wide open profiler trace, i.e. no filters, then you may end up "flooding" profiler and you won't be able to stop the trace. So, I would recommend using some filters, maybe database id if you have a specific database you are looking for and also becoming familiar with sp_trace_setstatus in case you need to stop your profiler trace and can't.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • February 10, 2012 at 10:15 am

    #1445683

    Awesome!

    Thanks a lot, I got what I exactly wanted..

  • February 10, 2012 at 10:18 am

    #1445686

    Great. Glad that worked.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • February 10, 2012 at 10:43 am

    #1445694

    David Benoit (2/10/2012)

    I would have the following to be sure that I was able to catch what you want;

    SP:Completed

    SP:StmtCompleted

    SQL:BatchCompleted

    SQL:BatchStarting

    SQL:StmtCompleted

    If capturing the objects in red above I would also capture the following as I like to see what was passed during the remote procedure call. It can be helpful in troubleshhoting SP activity

    RPC:Completed

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • February 10, 2012 at 12:04 pm

    #1445722

    David Benoit (2/10/2012)

    sunny.tjk (2/10/2012)

    I've the following events and no filters

    SQL:BatchStarting

    SQL:BatchCompleted

    Audit Schema Object Access Event

    Existing Connection

    RPC completed

    Audit Login

    Audit Logout

    Unless you are looking for connection information to be collected I would remove the Audit Login / Logout and Existing Connection. Not sure why you have Audit Schema... selected.

    I would have the following to be sure that I was able to catch what you want;

    SP:Completed

    SP:StmtCompleted

    SQL:BatchCompleted

    SQL:BatchStarting

    SQL:StmtCompleted

    You can use the "Starting" version for those as well if wanted either in addition to or in place of the "Completed".

    Play around with that and see what you get. One note here but if you have a really busy server and you run a wide open profiler trace, i.e. no filters, then you may end up "flooding" profiler and you won't be able to stop the trace. So, I would recommend using some filters, maybe database id if you have a specific database you are looking for and also becoming familiar with sp_trace_setstatus in case you need to stop your profiler trace and can't.

    With StmtCompleted, you can actually start losing calls as the buffer in the trace gets full. I wouldn't suggest capturing statement level stuff until people are really sure they need it. Most don't. Plus it adds just tons and tons of overhead to manage.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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