Reading the transaction log in SQL Server

To see transactions for inserted rows:

SELECT [Current LSN],
  Context, 
  [Transaction ID], 
  [Begin time],
  [Operation],
  [Transaction Name],
  [Transaction SID],
  [SPID],
  [Begin Time],
  [AllocUnitName],
  [Page ID],
  [Slot ID],
  [End Time],
  [Number of Locks],
  [Lock Information]
  FROM sys.fn_dblog(NULL, NULL)
  WHERE operation IN('LOP_INSERT_ROWS');

Alternatively, to view additional transaction operations, you can use:

-- OR
WHERE Operation IN('LOP_INSERT_ROWS','LOP_MODIFY_ROW','LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT')

To check for transactions related to a split page, use the following query:

-- Or for a split page
WHERE [Transaction Name]='SplitPage'

To see transactions for deleted rows:

-- To see transactions for deleted rows
SELECT [begin time], 
       [rowlog contents 1], 
       [Transaction Name], 
       Operation
  FROM sys.fn_dblog(NULL, NULL)
  WHERE operation IN('LOP_DELETE_ROWS');

If you need to read native transaction log backups, use the following query:

-- To read native transaction log backups
SELECT [Current LSN], Context, [transaction name],
       Operation, [Transaction ID], Description
FROM fn_dump_dblog(NULL,NULL,N'DISK',1,
  N'F:\\Mybackups\\InventoryDatabase_4589725r.trn', 
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT, 
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT,
  DEFAULT,DEFAULT,DEFAULT,DEFAULT);

To recover a dropped object from SQL Server log, use the following:

-- Can recover the dropped object from SQL Server log
SELECT Convert(varchar(Max), Substring([RowLog Contents 0], 33, LEN([RowLog Contents 0]))) AS [Script]
FROM fn_dblog(NULL,NULL)
WHERE [Operation] = 'LOP_DELETE_ROWS' 
AND [Context] = 'LCX_MARK_AS_GHOST'
AND [AllocUnitName] = 'sys.sysobjvalues.clst'

SELECT Convert(varchar(Max), Substring([RowLog Contents 0], 33, LEN([RowLog Contents 0]))) AS [Script]
FROM fn_dblog(NULL,NULL)
WHERE [Operation] = 'LOP_DELETE_ROWS' 
AND [Context] = 'LCX_MARK_AS_GHOST'
AND [AllocUnitName] = 'sys.sysobjvalues.clst'
AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM sys.fn_dblog(NULL, NULL) 
WHERE Context IN ('LCX_NULL') 
AND Operation IN ('LOP_BEGIN_XACT')  
AND [Transaction Name] = 'DROPOBJ'  
AND CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)
GO

To restore the log with a specific LSN number, use the following:

-- Restore log with LSN number
RESTORE LOG InventoryDatabase
FROM DISK = N'F:\\Mybackups\\InventoryDatabase_4589725r.trn'
WITH STOPBEFOREMARK = 'lsn:112000000001700001',
    NORECOVERY;

Finally, to check for databases with more than 100 VLFs, run this query:

-- Returns the names of the databases with more than 100 VLFs
SELECT [name], COUNT(l.database_id) AS 'vlf_count'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name]
HAVING COUNT(l.database_id) > 100;