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)
GO
As 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