1

I have used these queries to access transactions from an SQL Server database

Use Action
Go
Select * from fn_dblog(null,null)

This query works but only loads 7 lines when one table in database holds over 100,000 records Next I used this query to recover history

SELECT  * FROM fn_dump_dblog(NULL,NULL,'DISK',1
,'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Action.bak'
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,   NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,   NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,   NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,   NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL,   NULL,NULL,NULL,NULL,NULL,NULL
,NULL,NULL,NULL,NULL)

This query worked without error but only recovered 37 lines. If I restore the backup there are more than 100,000 records in one table. I would expect to see hundreds or thousands of lines when I run these queries.

The only logical answer would be that the transactions in the file are limited. But the file size for the transaction log is 1.5 GB. Is there any command I can run to check the number of transactions in the log file?

Can anyone explain how I can make these queries work to recover every transaction in the transaction log?

  • 2
    Do you think your active transaction log contains every transaction from all of time? – Aaron Bertrand Dec 26 '16 at 0:10
7

This query works but only loads 7 lines when one table in database holds over 100,000 records

The thing with sys.fn_dblog is that it only shows you the active portion of the log. If you wanted every transaction that ever happened through means such as this, I might suggest something more useful of your time.

This query worked without error but only recovered 37 lines. If I restore the backup there are more than 100,000 records in one table. I would expect to see hundreds or thousands of lines when I run these queries.

A single log backup doesn't hold everything that ever happened, you'll need every log backup ever taken. If your database was ever in the simple recovery model, or if it was in the full recovery model and anything was accomplished in it before a full backup was taken then you're not going to get everything back. Period.

Just because there are 100k records in the table doesn't mean there were 100k transactions, either. It could have been one big transaction, it could have been 2 transactions that inserted 200k and then a 3rd transaction that removed 100k, etc. ad nauseam.

The only logical answer would be that the transactions in the file are limited.

Yes in terms of what you can read at any given point as the log file can be thought of as a giant ring buffer that writes over the older inactive portions. It's circular and will continue re-using inactive portions unless it fills up and must grow because all of the current space is active.

Is there any command I can run to check the number of transactions in the log file?

You already have them, you just need to do this for every log backup ever taken across all of the time the database has been in existence.

Also, this doesn't take into account actually reading the log structure which isn't publically documented. This doesn't even include the newer log structures for things like Hekaton which have their own structure inside of the log structures or any of the trace flags you'd need to set certain behaviors.

All-in-all you're better off doing something else with your time. If this is needed as part of database recovery then I'd employ some professionals. If this is purely for academic then I would suggest creating a very small database, putting it in the full recovery model, taking a full backup, and not to take a log backup until you're done looking at that part of the log.

  • Thanks for the advice. I have been trying to learn forensics and finding deleted records. Do you have any suggestions on where to learn this – Tim Dec 27 '16 at 20:08
  • @Tim Currently, the best you're going to get is what you've been doing - except in my last paragraph where I state to use the full recovery model and don't take a log backup until you're done investigating. Unfortunately the log record internals are not publically documented and their structures may change between versions. This also doesn't take into account trace flags that may be useful... and also not documented. – Sean says Remove Sara Chipps Dec 27 '16 at 23:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.