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. 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
  2. Shehap MVP, MCTS, MCITP SQL Server

    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')
  3. 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?
  4. Shehap MVP, MCTS, MCITP SQL Server

    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

Share This Page