Post reply

what would be optimal event selection for SQL profiler?

  • January 29, 2014 at 12:22 am

    #285053

    Hi there all,

    I have a user that whenever they are not around the system is 100% fine but the second they are back we have issues ie: invoices coming out blank, SP's failing, etc..

    What would be the optimal selection for the event selection from profiler to see all that this user is doing?

    so far I have:

    objects-objects altered

    security audit- audit login

    T-SQL- exec prepaired SQL

    - SQL: stmtcompleted

  • January 29, 2014 at 3:05 am

    #1684203

    Unless you can narrow your trace down to a specific user login and you are only going to run the Profiler trace for a short period of time, I suggest you use a server-side trace instead as it is lighter weight in terms of the impact on resources.

    If you are going to use Profiler then I'd include SQL:BatchCompleted from the TSQL event class and RPC:Completed from the Stored Procedures event class instead of the two TSQL events you've stated.

    There is a plug-in available for Extended Events in SQL 2008 R2; Extended Events are very lightweight and are much more flexible than Profiler traces, but they do take some time to understand.

    Look in BOL for more infor about server-side tracing and Extended Events. Also check out the links below.

    http://technet.microsoft.com/en-us/library/cc293613.aspx

    http://technet.microsoft.com/en-us/library/bb630282(v=sql.105).aspx

    Regards

    Lempster

  • January 29, 2014 at 3:21 am

    #1684211

    Thanks Lempster

    I have already added the filter for only the login of the user I wish to track, I will also check the books online.

Viewing 3 posts - 1 through 2 (of 2 total)

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