Need to look into Log file. RRS feed

  • Question

  • Is there any way to read log file in SQL server
    Friday, August 30, 2013 10:34 PM


  • You can read log file by below command

    DBCC Loginfo

    Friday, August 30, 2013 10:45 PM
  • Not really, but what are  you looking for? For example you can identify transactions that dropped the table

    The following code works for 2000, 2005, and 2008:

    SELECT [Transaction Id], [Begin Time], [UID], [SPID]
    FROM ::fn_dblog (NULL, NULL)
    WHERE [Transaction Name] = 'DROPOBJ'

    You can turning on trace flag 2537. It allows the function to look at *all* possible log, not just the active log.

    Select [PAGE ID],[Slot ID],[AllocUnitId],[Transaction ID] ,[RowLog Contents 0]
    , [RowLog Contents 1],[RowLog Contents 3],[RowLog Contents 4] ,[Log Record]
    FROM    sys.fn_dblog(NULL, NULL)
    WHERE AllocUnitId IN
    (Select [Allocation_unit_id] from sys.allocation_units allocunits
    INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
    AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2  AND partitions.partition_id = allocunits.container_id)
    Where object_id=object_ID('' + 'dbo.student' + ''))
    And [Context] IN   ('LCX_HEAP','LCX_CLUSTERED')

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Saturday, August 31, 2013 8:39 AM

All replies