1

Using the fn_dblog I can get the active portion of the online transaction log of my database.

But after every checkpoint I can only see 2 entries showing the beginning of the checkpoint LOP_BEGIN_CKPT and the end of the checkpoint LOP_END_CKPT.

I'm wondering whether if the previous logs removed after every checkpoint or they are only ignored because the dbi_checkptLSN value that located in the bootpage indicate to the latest LSN?

If it is result of the dbi_checkptLSN am I able to move that indicator, and if yes how?

If not, and if the logs is not removed how can I access the previous logs?

2 Answers 2

3

The transactions are not actually removed after a check point. Their state is only changed to inactive, so they are ignored by the fn_dblog function

Even when the transactions are in the inactive parts of the online transaction log, it doesn't mean that they are deleted from the LDF file

"the VLF is marked as truncated (meaning the VLF can be overwritten once the transaction log wraps"

Understanding Logging and Recovery in SQL Server

An inactive VLF can and will be overwritten, but not immediately after the checkpoint, so the transactions will be there for a while. They will be gone when new transactions overwrite them. That can be in 5 minutes, or in 2 days. It depends on several factors

Please note that 'truncate' doesn't mean 'deleted', just marked so it can be reused, i.e. overwritten.

When the checkpoint is performed, the inactive portion of the transaction log is marked as reusable. Thereafter, the inactive portion can be freed by log truncation. Truncation frees the inactive virtual log files for reuse. Eventually, when a new record is written to a freed virtual log, that virtual log file becomes active again.

Transaction Log Truncation

logs is not removed how can I access the previous logs?

Besides DBCC log, there are third party tools that read LDF files, such as ApexSQL Log.

Disclaimer: I work for ApexSQL as a Support Engineer

6

If your database is in simple recovery the log might be truncated after a checkpoint.

This does not always occur though for reasons discussed in Factors That Can Delay Log Truncation (the most obvious being an open transaction)

To see the inactive portion of the log you can use the undocumented DBCC log command.

DBCC log(0,1) /*First parameter is database id with 0 as current DB*/

or trace flag 2537 in conjunction with sys.fn_dblog

DBCC TRACEON(2537)

SELECT *
FROM sys.fn_dblog(NULL,NULL)

Of course there is no guarantee that the thing you are looking for will be there. It may be overwritten at any moment.

1
  • 3
    Another command that make use of a lot regarding the log is DBCC LOGINFO which shows all current VLFs. Status 2 VLFs are still active and waiting to be truncated.
    – Mike Fal
    Dec 29, 2013 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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