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 :
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 🙂