Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. The transaction log may grow without a log backup for a database using the FULL recovery model

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

View products that this article applies to.

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.     

↑ Back to the top

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

↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

Keywords: kb

↑ Back to the top

Article Info
Article ID : 2523744
Revision : 1
Created on : 1/7/2017
Published on : 3/17/2011
Exists online : False
Views : 340