Look for deleted records in transaction log file.
USE MyDB
GO
SELECT 
    [Transaction ID],
    Operation,
    Context,
    AllocUnitName
FROM 
    fn_dblog(NULL, NULL) 
WHERE 
    Operation = 'LOP_DELETE_ROWS'
    AND
    AllocUnitName = 'dbo.tbTable01'SELECT
 [Current LSN],
 [Transaction ID],
 [Operation],
  [Transaction Name],
 [CONTEXT],
 [AllocUnitName],
 [Page ID],
 [Slot ID],
 [Begin Time],
 [End Time],
 [Number of Locks],
 [Lock Information]
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation IN 
   ('LOP_INSERT_ROWS','LOP_MODIFY_ROW',
    'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT')  There is an undocumented function called fn_dblog which enables you to read data from your transaction log which contains very informative data about things that are happening in your database.
The function fn_dblog requires a beginning LSN and ending LSN for a transaction. NULL is the default for this function and this will return all log records from the transaction log file.
How a backup interacts with the SQL Server transaction log
SELECT COUNT(*)
FROM fn_dblog(null,null)
GO
BACKUP DATABASE ReadingDBLog TO DISK = 'c:\ReadingDBLog_Full.bak'
GO
SELECT COUNT(*)
FROM fn_dblog(null,null)
GOAs we can see, the number of rows has been drastically reduced after doing a backup. This means the inactive part of the log which tracked the transactions has been dumped to a backup file and the original entries from the log file have been flushed. Now you can shrink the log file if necessary.
Sources:
https://www.mssqltips.com/sqlservertip/3076/how-to-read-the-sql-server-database-transaction-log/
Comments