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