SQL Error: 9002, Severity: 17, State: 2

Working fine with both SQL Server 2005 & 2008.

Error: 9002, Severity: 17, State: 2
The transaction log for database ‘addarr_wordpress’ 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 A & B situations.

A. If the log file has been configured with preset max size limit then the file is full.
B. If the log file has been configured with unlimited size then perhaps the disk is full.

If it is the B 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 10 or 100 of reasons that could come up in this column and some of them are noted here we go ..
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 tell 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.

A. Change the recovery model to SIMPLE
B. Shrink the log file.
C. Change the recovery model to FULL
D. 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 Database 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.

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 re-use wait time. It gives you a rough estimate of the amount of time required to complete it.
SELECT command,
            s.text,
            start_time,
            percent_complete,
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ‘ hour(s), ‘
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + ‘min, ‘
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ‘ sec’ as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ‘ hour(s), ‘
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + ‘min, ‘
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ‘ sec’ as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in (‘RESTORE DATABASE’, ‘BACKUP DATABASE’, ‘RESTORE LOG’, ‘BACKUP LOG’)

Welcome any comments on this issue.

Mehboob – MCTS – MCITP SQL & SharePoint Admin

Advertisements
%d bloggers like this: