The transaction log for database ‘mydatabase’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Very often clients come to us requesting assistance because the log file is full and the transactions won’t complete anymore. They try to fix this problem by expanding the size of the log file or adding more space to the disk drive where the log file is located. Although this gives them some breathing room, very soon they land in the same situation as before.
SQL Server logging mechanism is not that complicated but it’s best to leave the log file management to the experts (DBAs). I will try to explain the most common reason why the log file will get full.
Databases in SQL Server can be in either SIMPLE recovery mode or in FULL recovery mode. In SIMPLE recovery mode as soon as the transactions are committed the log space can be re-utilized. But SIMPLE mode does not give you the option of recovering the database using log backups.
If your database is in SIMPLE recovery mode and the log file is full then either the file size is small for the amount of active transactions or you have run out disk space.
But if your database is in FULL recovery mode and the transaction log file is full then follow these steps to fix the problem.
If the log file is configured with a preset max size then see if you can increase that for now.
Now lets look at why the file got full in the first place. First thing that you need to check is the log_reuse_wait_desc column in the sys.databases.
select name, recovery_model_desc, log_reuse_wait_desc from sys.databases
There are several reasons that could come up in this column and some of them are noted here.
NOTHING CHECKPOINT LOG_BACKUP ACTIVE_BACKUP_OR_RESTORE ACTIVE_TRANSACTION DATABASE_MIRRORING REPLICATION DATABASE_SNAPSHOT_CREATION LOG_SCAN OTHER_TRANSIENT
If the database in question is TEMPDB then the process to resolve it would be different and also the reasons for which TEMPDB gets full are different. But let me discuss the most common reason why a user database’s log file gets full.
LOG_BACKUP
In most cases you will see the reason noted in ‘log_reuse_wait_desc’ is given as ‘LOG_BACKUP’. This means that the database is in FULL recovery model and is waiting for a log backup to be taken.
If you have scheduled a regular log backup job then check its status and wait for it to finish before you shrink the log file. If you check the free space in the log file then you will indeed see a lot of unused space but you can not shrink it. Once the log backup completes you can shrink the file.
You can run the following TSQL to get the amount of free space vs used space in the log file.
select [FileSizeMB] = convert(numeric(10,2),round(a.size/128.,2)), [UsedSpaceMB] = convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) , [UnusedSpaceMB] = convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) , [DBFileName] = a.name from sysfiles a
But if the data file is not as big as the log file then instead of doing a log backup, I would do the following.
- Change the recovery model to SIMPLE
- Run a CHECKPOINT.
- Shrink the log file
- Change the recovery model to FULL
- Take a full backup and subsequently schedule log backups.
Sometimes the above steps take a lot less time to complete than taking a log backup and then shrinking the file. But please keep in mind that when you do this you have essentially broken the log chain and will have to resync the database if it is configured for log shipping.
The question of whether to truncate the log or not is dependent on the DB size. If it is not too big then truncate it and take a full backup. Otherwise it is best to take log backups.
ACTIVE_TRANSACTION
Another prominent reason that I have seen is ‘ACTIVE_TRANSACTION’. In this case, it would be best if you first add a new log file to the database or extend it. Then run the DBCC OPENTRAN on that database and check the open transactions. This should give you more information about the transaction that is consuming most of the log space and has not yet completed.
If the reason given is ACTIVE_BACKUP_OR_RESTORE then refer to my earlier post to find what is the expected time to finish the current backup or restore.
If the reason is related to either replication or mirroring then first check the status of replication or mirroring to ensure that they are up to speed and don’t have any latency. This should help in reducing the log reuse wait time.