The transaction log may grow without a log backup for a database using the FULL recovery model

Symptoms

The size of the transaction log may continue to grow unexpectedly for a database that uses FULL recovery model until a log backup is completed.     

Cause

There are several possible causes for the transaction log to grow unexpectedly. One of them is that a full database backup has been completed but not a recent log backup. Once a full database backup is completed for a database using the FULL recovery model, the log will not be truncated until a log backup is completed.

For other possible causes of a transaction log that continues to grow unexpectedly please see the following:

  • 873235 Recover from a full transaction log in a SQL Server database

Resolution

Complete a backup of the transaction log using the BACKUP LOG command. For more information about log backups, please see the section titled Creating Transaction Log Backupsin the SQL Server Books Online.

More Information

When you first create a database using the FULL recovery model, the transaction log will be reused as needed (similar to a SIMPLE recovery database), up until the time you create a full database backup. The log can be safely reused in this situation because media recovery of the log is not possible without a log backup (and a log backup cannot be completed until a full database backup is completed first). Once a full database backup has been completed, all log records that have not been backed up must remain in the log to preserve the log chain for possible media recovery until the log is backed up.

One method you can use to see if you have a recent log backup for your database is to use the following T-SQL query

select top 1 [backupsets].backup_finish_date, [backupsets].type

from msdb.dbo.backupset [backupsets]

join sys.databases [databases]

on [backupsets].[database_name] = [databases].[name]

and datediff ( ss, [databases].[create_date] , [backupsets].[database_creation_date] ) = 0

where [databases].database_id = <dbid>

and [backupsets].recovery_model = 'FULL'

order by [backupsets].backup_finish_date desc

If the above query does not return any rows, then either your database is using SIMPLE recovery (or BULK-LOGGED) model or the database has never been backed up. The query should only return 1 row and is designed to give you the type for the latest backup of your database. If the type = 'L', then the last backup is a log backup. Any other type means you likely need to complete a log backup to avoid any log growth.

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×