SQL Server Error : 9002 Severity: 17. The transaction log for database ‘%.*ls’

SQL Server Error : 9002 Details

SQL Server Error: 9002
Severity: 17
Event Logged or not: Yes
Description:
The transaction log for database ‘%.*ls’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Severity 17 Description:
Indicates that the statement caused SQL Server to run out of resources (such as memory, locks, or disk space for the database) or to exceed some limit set by the system administrator.

The SQL Server Database Engine error occurs when the transaction log is complete. The log can be filled in online or recovery time the database is in. The database remains online but can only be read and not updated if the log fills while the database is online. The Database Engine marks the database as RESOURCE PENDION when the log is completed during recovery. User actions are required in either case to make access to log space.

Common reasons for a full transaction log

The appropriate response to a full transaction log is determined by the conditions that caused the log to fill. Common causes include:

1. The log is not truncated.
2. Disk volume is full; log size is set to a fixed maximum value; or autogrow is disabled.
3. Replication or availability group synchronisation that fails to complete

Solution on How to Resolve a Full Transaction Log?

The specific steps below will assist you in determining the cause of a full transaction log and resolving the issue.

1. Truncate the Log
A very common solution to this problem is to ensure that your database’s transaction log backups are performed, which will ensure that the log is truncated. If there is no recent transaction log history for the database with a full transaction log, the solution is simple: resume regular transaction log backups of the database.

Explanation of log truncation

There is a distinction between truncating and shrinking a transaction log. Log Truncation is a logical operation that removes committed records from the log during a transaction log backup, whereas log shrinking reclaims physical space on the file system by reducing log size.

 

The following set of T-SQL commands will help you identify if a database transaction log is not truncated and the reason for it why its able to truncate. The following script will also recommend steps to resolve the issue referenced here

https://docs.microsoft.com/en-us/sql/relational-databases/logs/troubleshoot-a-full-transaction-log-sql-server-error-9002?view=sql-server-ver15

 

SET NOCOUNT ON
DECLARE @SQL VARCHAR (8000), @log_reuse_wait tinyint, @log_reuse_wait_desc nvarchar(120), @dbname sysname, @database_id int, @recovery_model_desc varchar (24)

IF ( OBJECT_id (N’tempdb..#CannotTruncateLog_Db’) is not null)
BEGIN
DROP TABLE #CannotTruncateLog_Db
END

–get info about transaction logs in each db. Use a DMV which supports all supported versions

IF ( OBJECT_id (N’tempdb..#dm_db_log_space_usage’) is not null)
BEGIN
DROP TABLE #dm_db_log_space_usage
END
SELECT * INTO #dm_db_log_space_usage FROM sys.dm_db_log_space_usage where 1=0

DECLARE log_space CURSOR FOR SELECT NAME FROM sys.databases
OPEN log_space

FETCH NEXT FROM log_space into @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

set @SQL = ‘
insert into #dm_db_log_space_usage (
database_id,
total_log_size_in_bytes,
used_log_space_in_bytes,
used_log_space_in_percent,
log_space_in_bytes_since_last_backup
)
select
database_id,
total_log_size_in_bytes,
used_log_space_in_bytes,
used_log_space_in_percent,
log_space_in_bytes_since_last_backup
from ‘ + @dbname +’.sys.dm_db_log_space_usage’

BEGIN TRY
exec (@SQL)
END TRY

BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

FETCH NEXT FROM log_space into @dbname
END

CLOSE log_space
DEALLOCATE log_space

–select the affected databases
SELECT
sdb.name as DbName,
sdb.log_reuse_wait, sdb.log_reuse_wait_desc,
log_reuse_wait_explanation = CASE

WHEN log_reuse_wait = 1 THEN ‘No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond’
WHEN log_reuse_wait = 2 THEN ‘A log backup is required before the transaction log can be truncated.’
WHEN log_reuse_wait = 3 THEN ‘A data backup or a restore is in progress (all recovery models). Please wait or cancel backup’
WHEN log_reuse_wait = 4 THEN ‘A long-running active transaction or a defferred transaction is keeping log from being truncated. You can attempt a log backup to free space or complete/rollback long transaction’
WHEN log_reuse_wait = 5 THEN ‘Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database. (Full recovery model only)’
WHEN log_reuse_wait = 6 THEN ‘During transactional replication, transactions relevant to the publications are still undelivered to the distribution database. Investigate the status of agents involved in replication or Changed Data Capture (CDC). (Full recovery model only.)’

WHEN log_reuse_wait = 7 THEN ‘A database snapshot is being created. This is a routine, and typically brief, cause of delayed log truncation.’
WHEN log_reuse_wait = 8 THEN ‘A transaction log scan is occurring. This is a routine, and typically a brief cause of delayed log truncation.’
WHEN log_reuse_wait = 9 THEN ‘A secondary replica of an availability group is applying transaction log records of this database to a corresponding secondary database. (Full recovery model only.)’
WHEN log_reuse_wait = 13 THEN ‘If a database is configured to use indirect checkpoints, the oldest page on the database might be older than the checkpoint log sequence number (LSN).’
WHEN log_reuse_wait = 16 THEN ‘An In-Memory OLTP checkpoint has not occurred since the last log truncation, or the head of the log has not yet moved beyond a VLF.’
ELSE ‘None’ END,

sdb.database_id,
sdb.recovery_model_desc,
lsu.used_log_space_in_bytes/1024 as Used_log_size_MB,
lsu.total_log_size_in_bytes /1024 as Total_log_size_MB,
100 – lsu.used_log_space_in_percent as Percent_Free_Space
INTO #CannotTruncateLog_Db
FROM sys.databases AS sdb INNER JOIN #dm_db_log_space_usage lsu ON sdb.database_id = lsu.database_id
WHERE log_reuse_wait > 0

SELECT * FROM #CannotTruncateLog_Db

DECLARE no_truncate_db CURSOR FOR
SELECT log_reuse_wait, log_reuse_wait_desc, dbname, database_id, recovery_model_desc FROM #CannotTruncateLog_Db;

OPEN no_truncate_db

FETCH NEXT FROM no_truncate_db into @log_reuse_wait, @log_reuse_wait_desc, @dbname, @database_id, @recovery_model_desc

WHILE @@FETCH_STATUS = 0
BEGIN
if (@log_reuse_wait > 0)
select ‘– ”’ + @dbname + ”’ database has log_reuse_wait = ‘ + @log_reuse_wait_desc + ‘ –‘ as ‘Individual Database Report’

if (@log_reuse_wait = 1)
BEGIN
select ‘Consider running the checkpoint command to attempt resolving this issue or further t-shooting may be required on the checkpoint process. Also, examine the log for active VLFs at the end of file’ as Recommendation
select ‘USE ”’ + @dbname+ ”’; CHECKPOINT’ as CheckpointCommand
select ‘select * from sys.dm_db_log_info(‘ + CONVERT(varchar,@database_id)+ ‘)’ as VLF_LogInfo
END
else if (@log_reuse_wait = 2)
BEGIN
select ‘Is ‘+ @recovery_model_desc +’ recovery model the intended choice for ”’ + @dbname+ ”’ database? Review recovery models and determine if you need to change it. https://docs.microsoft.com/sql/relational-databases/backup-restore/recovery-models-sql-server’ as RecoveryModelChoice
select ‘To truncate the log consider performing a transaction log backup on database ”’ + @dbname+ ”’ which is in ‘ + @recovery_model_desc +’ recovery model. Be mindful of any existing log backup chains that could be broken’ as Recommendation
select ‘BACKUP LOG [‘ + @dbname + ‘] TO DISK = ”some_volume:\some_folder\’ + @dbname + ‘_LOG.trn ”’ as BackupLogCommand
END
else if (@log_reuse_wait = 3)
BEGIN
select ‘Either wait for or cancel any active backups currently running for database ”’ +@dbname+ ”’. To check for backups, run this command:’ as Recommendation
select ‘select * from sys.dm_exec_requests where command like ”backup%” or command like ”restore%”’ as FindBackupOrRestore
END
else if (@log_reuse_wait = 4)
BEGIN
select ‘Active transactions currently running for database ”’ +@dbname+ ”’. To check for active transactions, run these commands:’ as Recommendation
select ‘DBCC OPENTRAN (”’ +@dbname+ ”’)’ as FindOpenTran
select ‘select database_id, db_name(database_id) dbname, database_transaction_begin_time, database_transaction_state, database_transaction_log_record_count, database_transaction_log_bytes_used, database_transaction_begin_lsn, stran.session_id from sys.dm_tran_database_transactions dbtran left outer join sys.dm_tran_session_transactions stran on dbtran.transaction_id = stran.transaction_id where database_id = ‘ + CONVERT(varchar, @database_id) as FindOpenTransAndSession
END

else if (@log_reuse_wait = 5)
BEGIN
select ‘Database Mirroring for database ”’ +@dbname+ ”’ is behind on synchronization. To check the state of DBM, run the commands below:’ as Recommendation
select ‘select db_name(database_id), mirroring_state_desc, mirroring_role_desc, mirroring_safety_level_desc from sys.database_mirroring where mirroring_guid is not null and mirroring_state <> 4 and database_id = ‘ + convert(sysname, @database_id) as CheckMirroringStatus

select ‘Database Mirroring for database ”’ +@dbname+ ”’ may be behind: check unsent_log, send_rate, unrestored_log, recovery_rate, average_delay in this output’ as Recommendation
select ‘exec msdb.sys.sp_dbmmonitoraddmonitoring 1; exec msdb.sys.sp_dbmmonitorresults ”’ + @dbname+ ”’, 5, 0; waitfor delay ”00:01:01”; exec msdb.sys.sp_dbmmonitorresults ”’ + @dbname+ ”’; exec msdb.sys.sp_dbmmonitordropmonitoring’ as CheckMirroringStatusAnd
END

else if (@log_reuse_wait = 6)
BEGIN
select ‘Replication transactions still undelivered from publisher database ”’ +@dbname+ ”’ to Distribution database. Check the oldest non-distributed replication transaction. Also check if the Log Reader Agent is running and if it has encoutered any errors’ as Recommendation
select ‘DBCC OPENTRAN (”’ + @dbname + ”’)’ as CheckOldestNonDistributedTran
select ‘select top 5 * from distribution..MSlogreader_history where runstatus in (6, 5) or error_id <> 0 and agent_id = find_in_mslogreader_agents_table order by time desc ‘ as LogReaderAgentState
END

else if (@log_reuse_wait = 9)
BEGIN
select ‘Always On transactions still undelivered from primary database ”’ +@dbname+ ”’ to Secondary replicas. Check the Health of AG nodes and if there is latency is Log block movement to Secondaries’ as Recommendation
select ‘select availability_group=cast(ag.name as varchar(30)), primary_replica=cast(ags.primary_replica as varchar(30)),primary_recovery_health_desc=cast(ags.primary_recovery_health_desc as varchar(30)), synchronization_health_desc=cast(ags.synchronization_health_desc as varchar(30)),ag.failure_condition_level, ag.health_check_timeout, automated_backup_preference_desc=cast(ag.automated_backup_preference_desc as varchar(10)) from sys.availability_groups ag join sys.dm_hadr_availability_group_states ags on ag.group_id=ags.group_id’ as CheckAGHealth
select ‘SELECT group_name=cast(arc.group_name as varchar(30)), replica_server_name=cast(arc.replica_server_name as varchar(30)), node_name=cast(arc.node_name as varchar(30)),role_desc=cast(ars.role_desc as varchar(30)), ar.availability_mode_Desc, operational_state_desc=cast(ars.operational_state_desc as varchar(30)), connected_state_desc=cast(ars.connected_state_desc as varchar(30)), recovery_health_desc=cast(ars.recovery_health_desc as varchar(30)), synhcronization_health_desc=cast(ars.synchronization_health_desc as varchar(30)), ars.last_connect_error_number, last_connect_error_description=cast(ars.last_connect_error_description as varchar(30)), ars.last_connect_error_timestamp, primary_role_allow_connections_desc=cast(ar.primary_role_allow_connections_desc as varchar(30)) from sys.dm_hadr_availability_replica_cluster_nodes arc join sys.dm_hadr_availability_replica_cluster_states arcs on arc.replica_server_name=arcs.replica_server_name join sys.dm_hadr_availability_replica_states ars on arcs.replica_id=ars.replica_id join sys.availability_replicas ar on ars.replica_id=ar.replica_id join sys.availability_groups ag on ag.group_id = arcs.group_id and ag.name = arc.group_name ORDER BY cast(arc.group_name as varchar(30)), cast(ars.role_desc as varchar(30))’ as CheckReplicaHealth
select ‘select database_name=cast(drcs.database_name as varchar(30)), drs.database_id, drs.group_id, drs.replica_id, drs.is_local,drcs.is_failover_ready,drcs.is_pending_secondary_suspend, drcs.is_database_joined, drs.is_suspended, drs.is_commit_participant, suspend_reason_desc=cast(drs.suspend_reason_desc as varchar(30)), synchronization_state_desc=cast(drs.synchronization_state_desc as varchar(30)), synchronization_health_desc=cast(drs.synchronization_health_desc as varchar(30)), database_state_desc=cast(drs.database_state_desc as varchar(30)), drs.last_sent_lsn, drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn, drs.last_hardened_time,drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size, drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate, drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time, drs.low_water_mark_for_ghosts, drs.recovery_lsn, drs.truncation_lsn, pr.file_id, pr.error_type, pr.page_id, pr.page_status, pr.modification_time from sys.dm_hadr_database_replica_cluster_states drcs join sys.dm_hadr_database_replica_states drs on drcs.replica_id=drs.replica_id and drcs.group_database_id=drs.group_database_id left outer join sys.dm_hadr_auto_page_repair pr on drs.database_id=pr.database_id order by drs.database_id’ as LogMovementHealth
select ‘For more information see https://docs.microsoft.com/en-us/troubleshoot/sql/availability-groups/error-9002-transaction-log-large’ as OnlineDOCResource
END
else if (@log_reuse_wait in (10, 11, 12, 14))
BEGIN
select ‘This state is not documented and is expected to be rare and short-lived’ as Recommendation
END
else if (@log_reuse_wait = 13)
BEGIN
select ‘The oldest page on the database might be older than the checkpoint log sequence number (LSN). In this case, the oldest page can delay log truncation.’ as Finding
select ‘This state should be short-lived, but if you find it is taking a long time, you can consider disabling Indirect Checkpoint temporarily’ as Recommendation
select ‘ALTER DATABASE [‘ +@dbname+ ‘] SET TARGET_RECOVERY_TIME = 0’ as DisableIndirectCheckpointTemporarily
END
else if (@log_reuse_wait = 16)
BEGIN
select ‘For memory-optimized tables, an automatic checkpoint is taken when transaction log file becomes bigger than 1.5 GB since the last checkpoint (includes both disk-based and memory-optimized tables)’ as Finding
select ‘Review https://blogs.msdn.microsoft.com/sqlcat/2016/05/20/logging-and-checkpoint-process-for-memory-optimized-tables-2/’ as ReviewBlog
select ‘use ‘ +@dbname+ ‘ CHECKPOINT’ as RunCheckpoint
END

FETCH NEXT FROM no_truncate_db into @log_reuse_wait, @log_reuse_wait_desc, @dbname, @database_id, @recovery_model_desc

END

CLOSE no_truncate_db
DEALLOCATE no_truncate_db

Take a look at the database’s recovery model.
Because you have never backed up the transaction log, it may fail to truncate with the LOG BACKUP log reuse wait category. In many of those cases, your database is using the FULL or BULK LOGGED recovery model, but you failed to backup transaction logs. To reduce the occurrences of error 9002, you should carefully consider each database recovery model: perform transaction log backups on all databases in FULL or BULK LOGGED recovery models. See Recovery Models for more information.

2. Backup the log of db

If the transaction log has not been backed up recently under the FULL or BULK LOGGED recovery models, backup may be what is preventing log truncation. To allow log records to be released and the log to be truncated, you must back up the transaction log. If the log has never been backed up, you must create two log backups to allow the Database Engine to truncate the log to the last backup point. Truncating the log makes logical space available for new log records. Take log backups on a regular and more frequent basis to keep the log from filling up again. See Recovery Models for more information.
The msdb database stores a complete history of all SQL Server backup and restore operations performed on a server instance.

4. Change log size limit or enable Autogrow

Error 9002 can be generated if the transaction log size has been set to an upper limit or Autogrow is not allowed. In this case, enabling autogrow or increasing the log size manually can help resolve the issue. Use T-SQL command to find such log files and follow the recommendations like

Increase the size of the log file or enable Autogrow.

You can increase the size of the log file if there is enough space on the log disc. The maximum size of a log file is two terabytes (TB).

If autogrow is disabled, the database is online, and enough disc space is available, perform one of the following:

Increase the file size manually to produce a single growth increment. These are general guidelines for log size growth and size.
Use the ALTER DATABASE statement to set a non-zero growth increment for the FILEGROWTH option to enable autogrow.

3. Resolve full disk volume

The disk volume that hosts the transaction log file may be fill up. You can take one of the following actions to resolve the log-full scenario that results from a full disk:

Free disk space
You can be able to free disk space on the disk drive that has the transaction log file for the database by moving other files or deleting them if not needed. The freed disk space allows the recovery system to enlarge the log file automatically.

Move the log file to a different disk
If we cannot free enough disk space on the drive that currently has the log file, consider moving the file to another drive with sufficient space altogether.

Reading sql server error log location from SQL Query

Identifying SQL Server Error Log File used by SQL Server Database Engine can be done by reading SQL Server Error Logs. DBA can execute the XP_READERRORLOG extended stored procedure to read the SQL Server Error Log and search for its location used by the instance of SQL Server.

USE master
Go
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'
Go

The parameters for XP_READERRRORLOG are:
1. Value of error log file we would like to read. values are 0 = current, 1 = last one before current, 2 = second last before current etc…
2. Log file type:- 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1:- String one you want to search for
4. Search string 2:- String two you want to search for to further refine the results
5. start time for Search
6. end time for search
7. Sort order for search results:- N’asc’ = ascending, N’desc’ = descending

By default, we have 6 Server Error Logs kept but we can increase the number of SQL Server Error Logs from the default value of six.

For other ways to read and find error log location please our artcile https://sqlserver-dba.co.uk/error-log/sql-server-identify-location-of-the-sql-server-error-log-file.html

SQL Server Error Code and solution summary

SQL Server Error: 9002
Severity: 17
Event Logged or not: Yes
Description:
The transaction log for database ‘%.*ls’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases