Can the same Inactive VLF appear in multiple TRN files

All we need is an easy explanation of the problem, so here it is.

I have a Fully Logged database that undergoes only a few changes on a daily basis.
And the Transaction Log backup is taken every hour.
A single-row Update (single transaction) takes place at 9:30am, the corresponding VLF is then truncated in the 10am log backup.

I understand that Truncated does not mean deleted, the VLF lingers around waiting to be overwritten.

The record is then again updated again at 10:30am.

So my question is, if there are no further transactions on the database, will the VLF (or data from it) appear in the TRN of the 11am Log backup?

i.e. can a full history of both Updates appear in the 11am TRN file?

I’m asking about this because I’m trying to make sense of something I’m seeing in some well known log reading software.

Thanks

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

A single-row Update (single transaction) takes place at 9:30am, the corresponding VLF is then truncated in the 10am log backup.

This isn’t (exactly) how it works. That log record will get backed up in the 10:00 am transaction log backup, but the VLF will only be cleared if it is "full" and transactions are being written to the "next" VLF.

Check out Paul Randal’s article on the subject for details:

Why is log_reuse_wait_desc saying LOG_BACKUP after doing a log backup?

Imagine a database where there’s very little insert/update/delete/DDL activity, so in between your regular log backups there are only a few log records generated, and they’re all in the same VLF. The next log backup runs, backing up those few log records, but it can’t clear the current VLF, so can’t clear log_reuse_wait_desc.

Your other question:

The record is then again updated again at 10:30am.

So my question is, if there are no further transactions on the database, will the VLF (or data from it) appear in the TRN of the 11am Log backup?

i.e. can a full history of both Updates appear in the 11am TRN file?

I don’t think so, although it would be interesting to know what you’re seeing in the log reading software that makes you question it.

The 10:00 am backup would have advanced the MinLSN as described in SQL Server Transaction Log Architecture and Management Guide, and the 11:00 am backup would only include the second UPDATE transaction.

Method 2

A Transaction Log backup should only contain what transactions have occurred on the database since the last time a Transaction Log backup was taken, and will not be inclusive of any previous Transaction Log backup’s transactions.

Every time a Transaction Log backup is taken, the Transaction Log file is internally emptied, and that space is re-used and overwritten by new transactions, as they occur.

To see the full history, you would need all related Transaction Log backups, to the point in time in the history you want to see.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply