Post reply

Audit table that holds Activity Monitor data

  • December 17, 2009 at 1:31 pm

    #137740

    I need to log connection data for certain hostnames connecting to a particular server. I want to log most of the data found in activity monitor including the process detail. I can get most of this from sp_who2, but what about the process detail? Not sure where I can query that from.

    I am planning to insert this data into an audit table using a trigger. It will pull any transaction made by the hostname select, update, etc.

    As anyone done something like this, any suggestions would be welcomed.

  • December 17, 2009 at 10:18 pm

    #1093869

    This sounds reasonable (have done a very similar thing).

    For the executing query text, this should get you headed in the right direction.

    select

    session_id

    ,start_time,command, st.text

    ,last_wait_type

    from sys.dm_exec_requests r

    cross apply sys.dm_exec_sql_text(sql_handle) AS st

    go

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • January 13, 2010 at 12:36 pm

    #1103245

    I was able to create the trigger for INSERT, UPDATE, DELETE, but can you fire a trigger off from a SELECT statement?

  • January 13, 2010 at 7:14 pm

    #1103389

    I don't think you can....

  • January 13, 2010 at 7:21 pm

    #1103390

    MVP Adam Machanic has done the work for you:

    http://sqlblog.com/blogs/adam_machanic/archive/2009/12/03/who-is-active-v9-57-fast-comprehensive-dmv-collection.aspx

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

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

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