SQL Server Performance

how to explore/ retrieve SQL Server Transaction Log information

Discussion in 'ALL SQL SERVER QUESTIONS' started by Muhammad Hasibul Hasan, Sep 14, 2012.

  1. Muhammad Hasibul Hasan New Member

    Can anyone advise me on how to explore/ retrieve SQL Server Transaction Log information e.g. interpret record of table/ row update/ delete command, including the information of host computers and user IDs, timestamps etc.?
    Or, Can anyone help me quoting any effective software that can do this on any previous transaction?
    Or, Can anyone advise me whether "eVisie TransactioneXplorer" or similar software can do that?

    My email:hasib.hasan@gmail.com
    Muhammad Hasibul Hasan, Sep 14, 2012
    #1

  2. Shehap Moderator

    First welcome to forums…

    Kindly work out the below query and let me know your feedback

    useworkshops
    SELECT[RowLog Contents 0],
    [RowLog Contents 1],
    [Current LSN],
    Operation,
    Context,
    [Transaction ID],
    AllocUnitId,
    AllocUnitName,
    [Page ID],
    [Slot ID]
    FROMsys.fn_dblog(NULL,NULL)
    WHEREContextIN('LCX_MARK_AS_GHOST','LCX_HEAP','LCX_CLUSTERED')
    ANDOperationIN('LOP_DELETE_ROWS','LOP_INSERT_ROWS')
    Shehap, Sep 14, 2012
    #2

  3. Muhammad Hasibul Hasan New Member

    Thanks a lot for quick reply. We have tried the query, but we need to retrieve host computer name, user ID etc from where the operation like table row update or delete command performed. Is it possible?
    Muhammad Hasibul Hasan, Sep 17, 2012
    #3

  4. Shehap Moderator

    I do think it is difficult to get such information through transaction log , but it is doable to get through building specific DMV monitor to collect the needed data through DMV and store them in a buffer table using a scheduled job..

    You can use such below DMV monitor

    CREATETABLE[dbo].[Sessions_Cache](
    [query_text][varchar](4000)NULL,
    last_execution_timeDATETIME,
    last_elapsed_timeINT,
    [APP_Server][varchar](100)NULL,
    [DB_ID]int,
    login_nmaevarchar (100)Null
    )ON[PRIMARY]

    insertintoSessions_Cache

    SELECT

    (SELECTtextFROMsys.dm_exec_sql_text(qs.sql_handle))ASquery_text,qs.last_execution_time,qs.last_elapsed_time/1000000,s.host_nameAPP_Server,s.database_id,s.login_time
    FROMsys.dm_exec_query_statsASqsinnerjoinsys.dm_exec_requestsRONqs.sql_handle=R.sql_handleINNERJOINsys.dm_exec_sessionssons.session_id=r.session_idWHEREqs.last_execution_timenotin(selectlast_execution_timefrommsdb.dbo.Sessions_Cachewith (nolock))
    orderbylast_elapsed_timedesc

    Kindly work out it and let me know your feedback
    Shehap, Sep 18, 2012
    #4

Share This Page