Identify and fix transaction log problems

Why is the transaction log disk full?

The transaction log of a SQL Server database keeps the transactional history of the writes made to that database.

  • If the recovery model of a database is FULL, the transaction log keeps everything, so that a transaction log backup can archive it.
  • If the recovery model of a database is SIMPLE, the transaction log is emptied regularly.

The problems this creates:

  • The recovery model of a newly created database is FULL by default.
  • A production database in FULL keeps that model when it is restored onto a development or staging server.
  • The transaction log of a database in FULL does not empty itself.

If a database is in FULL and no log backup (BACKUP LOG) is scheduled, the log grows without limit.

When that happens, there are only two options:

  1. schedule log backups, on a production server;
  2. switch the database to SIMPLE, on a non-production server.

Transaction log size

To see the size of your transaction logs and how much of them is used:

DBCC SQLPERF(LOGSPACE);

Why the transaction log does not empty

If you are in the FULL recovery model and the log is full, find out why:

SELECT
    name as [db],
    recovery_model_desc as [recovery],
    ISNULL(NULLIF(log_reuse_wait_desc, N'NOTHING'), '') as log_reuse_wait
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

The log_reuse_wait column can report:

  • LOG_BACKUP — check the log backup jobs.
  • ACTIVE_TRANSACTION — an active transaction is preventing the log from being emptied. You need to find and end that transaction, see the next section.
  • REPLICATION — the database is configured for replication or CDC. Check that it is working properly.
  • AVAILABILITY_REPLICA — the database belongs to an Always On availability group. Check that the secondary is synchronizing.

When log_reuse_wait is ACTIVE_TRANSACTION

An active transaction is holding the transaction log. To identify the sessions holding one open:

-- open sessions holding a transaction
SELECT *
FROM sys.dm_exec_sessions des
WHERE des.is_user_process = 1
AND des.open_transaction_count > 0;

Or in more detail with:

Active Transactions
See the file
----------------------------------------------------------
-- list all opened transactions with detail
-- rudi@babaluga.com, go ahead license
----------------------------------------------------------

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT  
	t.transaction_id,
	t.name,
	CAST(t.transaction_begin_time as datetime2(0)) as begin_time,
	DATEDIFF(SECOND, t.transaction_begin_time, CURRENT_TIMESTAMP) as tran_elapsed_time_seconds,
     CASE t.transaction_type   
          WHEN 1 THEN 'Read/Write'   
          WHEN 2 THEN 'Read-Only'    
          WHEN 3 THEN 'System'   
          WHEN 4 THEN 'Distributed'  
          ELSE CONCAT('Unknown - ', transaction_type)     
     END AS [type],    
     CASE t.transaction_state 
          WHEN 0 THEN 'Uninitialized' 
          WHEN 1 THEN 'Not Yet Started' 
          WHEN 2 THEN 'Active' 
          WHEN 3 THEN 'Ended (Read-Only)' 
          WHEN 4 THEN 'Committing' 
          WHEN 5 THEN 'Prepared' 
          WHEN 6 THEN 'Committed' 
          WHEN 7 THEN 'Rolling Back' 
          when 8 THEN 'Rolled Back' 
          ELSE CONCAT('Unknown - ', transaction_state) 
     END AS [state], 
     case t.dtc_state 
          WHEN 0 THEN NULL 
          WHEN 1 THEN 'Active' 
          WHEN 2 THEN 'Prepared' 
          WHEN 3 THEN 'Committed' 
          WHEN 4 THEN 'Aborted' 
          WHEN 5 THEN 'Recovered' 
          ELSE CONCAT('Unknown - ', dtc_state) 
     END AS [dtc state],
     db.name as db,
     db.log_reuse_wait_desc as log_reuse_wait,
     db.is_read_committed_snapshot_on as rcsi,
     dt.database_transaction_log_bytes_reserved as log_bytes_reserved,
     dt.database_transaction_log_bytes_used as log_bytes_used,
     dt.database_transaction_log_record_count as log_record_count,
     CAST(logSize.cntr_value / 1000.0 as numeric(20, 2)) as [log size MB],
     logPercent.cntr_value as [log %],
     st.session_id,
     st.transaction_descriptor as [tran descr],
     st.is_user_transaction as [user tran],
     st.open_transaction_count as [tran cnt],
     st.enlist_count as [stmt nb],
     se.login_time,
     se.host_name,
     se.program_name,
     se.login_name,
     se.status,
     inputbuffer.text as inputbuffer,
     CASE se.transaction_isolation_level
          WHEN 0 THEN 'Unspecified'
          WHEN 1 THEN 'Read Uncommitted'
          WHEN 2 THEN 'Read Committed'
          WHEN 3 THEN 'Repeatable Read'
          WHEN 4 THEN 'Serializable'
          WHEN 5 THEN 'Snapshot'
          ELSE CAST(se.transaction_isolation_level as varchar(50))
     END as isolation_level
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_tran_database_transactions dt ON t.transaction_id = dt.transaction_id
JOIN sys.databases db ON dt.database_id = db.database_id
JOIN sys.dm_os_performance_counters logSize ON db.name = logSize.instance_name
	AND logSize.object_name = 'SQLServer:Databases' AND logSize.counter_name = 'Log File(s) Size (KB)'
JOIN sys.dm_os_performance_counters logPercent ON db.name = logPercent.instance_name
	AND logPercent.object_name = 'SQLServer:Databases' AND logPercent.counter_name = 'Percent Log Used'
JOIN sys.dm_tran_session_transactions st ON t.transaction_id = st.transaction_id
LEFT JOIN sys.dm_exec_sessions se ON st.session_id = se.session_id
LEFT JOIN sys.dm_exec_connections cn ON cn.session_id = se.session_id
    OUTER APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) AS inputbuffer
ORDER BY t.transaction_begin_time
OPTION (RECOMPILE, MAXDOP 1);

Checking the recovery model

  • You can use this query to list the databases and their recovery model, in the recovery column.
  • My sp_databases stored procedure gives the same information.
  • DBCC SQLPERF (LOGSPACE) shows the logs and how full they are.
  • My sp_logspace stored procedure is a more useful replacement for DBCC SQLPERF (LOGSPACE).

Otherwise, this query is enough:

SELECT
    name as [db],
    recovery_model_desc as [recovery]
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

Changing the recovery model

This statement switches a database to the SIMPLE recovery model:

use [master];
GO

ALTER DATABASE [<database name>] SET RECOVERY SIMPLE WITH NO_WAIT

Then check with DBCC SQLPERF (LOGSPACE) that the log fill percentage has dropped.

Reclaiming the disk space

The physical size of the log does not shrink on its own. Only the used portion of the file changes.

To reduce the size of the log file you have to shrink it, for instance with:

USE [<database name>]
GO

DBCC SHRINKFILE (N'<logical name of the log file>' , 200)

This tries to reduce the file to 200 MB. It may not succeed straight away, if the active portion of the log sits at the end of the file. Try again later if the size has not gone down.

SQL Server Expertise Need help with SQL Server? Contact me