- Adam Thurgar
Reading a transaction log file backup
Have you ever had to find out what happened to your SQL Server. Who deleted that data? What event triggered something to happen. Sometimes all you have is a transaction log backup as your source of information. Reading a transaction log file backup is not that easy, but it isn't that hard either. You just need to use the undocumented fn_dump_dblog. I usually do this in a temporary database, to not interfere with anything else.
Here is a quick example:
USE ScratchDB
GO
SELECT * INTO DUMPLOG FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'V:\DBBackup\Tran Log\MY_LOG_20180702_124501.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);
and then start reading through the results
SELECT [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time], [PartitionID], [TRANSACTION SID]
from DUMPLOG where [Begin Time] > '2018/07/02 12:40:00:000' order by [Begin Time];
Hopefully from this you can track down that issue - or at least have a bit more information to help solve the puzzle.