What is the purpose of transactions that are not marked as inactive due to absence of truncate/log backup?

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

In full recovery model, log file size keeps growing. When the limit is reached, then the database no longer works.

To ensure this doesn’t happen we can either perform truncate or log backup regularly. Either options will ensure the log space can be re-used. It is said that these mark the inactive transactions so that the space can be re-used.

I want to ask – when the above operations (truncate or log backup) are not performed, then aren’t the transactions marked inactive? If so then what does it mean to have active transactions – what is their purpose?

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

Active transactions are those transactions that are still running on the server, or, those that haven’t yet been processed through replication or an Availability Group. They’re not marked as inactive because they’re still running, and therefore, you really don’t want them removed yet. Or, they haven’t moved through the process to ensure their data changes get captured, so you really don’t want them removed yet. You only want transactions that are truly completed to be purged from the log, preferably, after you backed them up so that you can restore to a point in time and have that restore be consistent.

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