Error: 9002, Severity: 17, State: 2
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
The transaction log file will get full in one of the following two situations.
1) If the log file has been configured with preset max size limit then the file is full.
2) If the log file has been configured with unlimited size then perhaps the disk is full.
If it is the second situation then first free up some space in the disk by moving some files or deleting some files.
Now lets look why the file got full. 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 DBs 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.
But if the data file is not as big as the log file then instead of doing a log backup, I will do the following.
1) Change the recovery model to SIMPLE
2) Shrink the log file.
3) Change the recovery model to FULL
4) 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
Other 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.
http://saveadba.blogspot.com/2011/10/backup-and-restore-progress.html
If the reason is related to either replication or mirroring then first check the status of replication or mirroring to ensure that they are upto speed and don't have any latency. This should help in reducing the log reuse wait time.
thanks!
ReplyDelete