SQL transaction logs increase in size with Simple backup

Question

Hi,

I have an SQL 2016 database setup with Simple recovery mode.
We have a nightly full backup running using SQL maintenance.

It is my understanding that if you have your database setup with simple recovery mode then your transaction log files should be marked as written every time you backup the database. As such the transaction logs should start overwriting and this will keep the transaction logs at a reasonable size.

Although we are doing nightly backups of the SQL the transaction logs increase in size every day (currently at 440GB) and the database backup is also increase in size every day. (Database backup if roughly the size of DB plus transaction logs.)

If I go to shrink the transaction logs it's telling me there is only 39GB of free space this is for a 440GB transaction log.

Can someone tell me why the transaction log is increase in size and not being overwriting when I have simple recovery mode set and completing a full backup.

Thanks for your time in advance.

Regards
Craig

Answer 1

Hi @Craig Garland

That is one of the biggest misunderstanding since the Simple Recovery model exists…

No, even on SIMPLE SQL server will still log every single transaction in the log but the backup handels those logs differently

https://www.red-gate.com/simple-talk/databases/sql-server/database-administration-sql-server/understanding-sql-server-recovery-models/

The MSFT documentation links to this similar document…
https://www.mssqltips.com/sqlservertutorial/4/sql-server-simple-recovery-model/

Answer 2

Hi,

I understand that transactions are written to the transaction logs no matter what recovery mode is set. The difference being that if recovery mode is set to Full then you need to backup the database and transaction logs and that a recovery would require restoring the DB and then the transaction logs. Simple recover mode only backup the DB and recovery you only recover the DB, and can only recover to the point in time the DB was backed up.

My understanding was that if you have a simple recovery mode then when you complete a backup the transaction logs should be marked to overwrite. Then new lines added to the transaction log will overwrite old transaction logs. This does not seem to be the case for me the transaction log file just keeps getting bigger.

Do I have this wrong?

Craig

Answer 3

Hi

So in your link it says "That removal process happens for all completed transactions when a checkpoint occurs. " I was under the impression that when you complete a full DB backup a checkpoint occurs.

Is that not the case?

Craig

Answer 4

Hi,

Please see this link that states checkpoint is created when taking a database backup.

https://www.sqlshack.com/database-checkpoints-enhancements-sql-server-2016/

"Internal Checkpoints are issued in response to several server events, such as the taking a database backup, creating a database snapshot, adding or removing database files, a clean shutdown for the SQL Server service is performed or 70% of the transaction log of a simple recovery model database is full."

Regards
Craig

Answer 5

Hi @Craig Garland ,

The SIMPLE recovery model is the simplest among the available models. It supports full, differential, and file level backups. Transaction log backups are not supported. The log space is reused whenever the SQL Server background process checkpoint operation occurs. The inactive portion of the log file is removed and is made available for reuse.
You can roughly calculate the percentage of your log files to your database. If this database is particularly large, it is not surprising that the log files are large.
The reason your disk file is getting large is probably due to the transaction log growth being based on a percentage (by default 10 percent) instead of a fixed amount. So if your transaction log file is 10MB and fills up, it grabs another 1MB disk space whether it needs all of it or not. If it's 10GB, it grabs a gig.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.