The transaction log for database ' XXXX ' was full due to ' active_transaction '

Today, we look at the job's history and find out that the job failed with the error message: "The transaction log for database ' XXXX ' was full due to ' active_transaction '."

The error message indicates that the transaction log file space for the database is exhausted and the log file can no longer store the new transaction log.

SQL Server logically divides the transaction log files into multiple VLF (Virtual Log file), which makes up a ring structure of VLF, which is a reusable unit. If an active Transaction exists in a VLF, then the VLF cannot be truncated and reused. If the transaction log file does not have a VLF available, SQL Server cannot process the new transaction and throws an error message that the transaction log file is exhausted.

So why is active Transaction causing the transaction log file to run out?

1, if the database transaction log file is too large, the entire disk space is exhausted, then you need to consider what causes the transaction log file growth, regular transaction log backups can truncate the transaction log files.

2, if the transaction log file for the database itself is not very large, the possible reason is that SQL Server cannot allocate disk Space for the transaction log files.

3, to view the active transactions in the database, if it is because a transaction has been running for too long and is not closed, the VLF of the transaction log cannot be reused, then the application must be modified.

If one of the Transaction in the database is running too long, causing the other Transaction to be commint, but the VLF it occupies is still marked as active, cannot be truncate and reuse, and no VLF is available in the log file, SQL Server will make an error when SQL Server processes the new transaction.

Step1, viewing the size of the transaction log file

Looking at the SIZE_GB and MAX_SIZE_GB fields of the log file, discovering that the transaction log file size does not reach the maximum, and that the transaction log file occupies a disk space that is not very large, I suspect that the disk space where the log file resides is exhausted. There is no free space left.

SelectDb.name asdatabase_name, db.is_auto_shrink_on, DB.RECOVERY_MODEL_DESC, MF.file_id, Mf.type_desc, Mf.name asLogic_file_name, Mf.size*8/1024x768/1024x768  asSIZE_GB, Mf.physical_name, IIF (Mf.max_size=-1,-1, mf.max_size*8/1024x768/1024x768) asMAX_SIZE_GB, Mf.growth, mf.is_percent_growth, Mf.state_desc fromsys.databases DBInner Joinsys.master_files MF ondb.database_id=mf.database_idwhereMf.size*8/1024x768/1024x768>1  --GB     andDb.name='Database name'     andMf.type=0Order  bySize_gbdesc

Step2, view disk's free Space

The query results show that the space in the D disk is only 9MB, which is the disk where the transaction log files reside.

exec sys.xp_fixeddrives

Step3,disk Space is exhausted, you must try to compress the large data files, or truncate the transaction log files.

Because the recovery model for the database is simple, the transaction log files are automatically truncated, so the biggest possibility is that disk space is exhausted.

1. View the usage of database space

exec sys.sp_spaceused

Unallocated space is very idle, you must compress the database to free disk space

2, shrink (shrink) database file

 UseTarget_database_nameGoSelect file_id, type, Type_desc, data_space_id, name, size*8/1024x768/1024x768  asSIZE_GB, Growth, is_percent_growth, physical_name, Max_size fromsys.database_filesDBCCShrinkfile ('file logcial name',0, Notruncate)DBCCShrinkfile ('file logcial name', target_size_mb,truncateonly)

3, the table and index compressed storage in the database
3.1, view the database, occupy a very large storage space table;

 UseTarget_database_nameGoSelectT.name,sum( Case  whenps.index_id<2  ThenPs.row_countElse 0 End) asRow_count,sum(Ps.reserved_page_count)*8/1024x768/1024x768  asRESERVED_GB,sum(Ps.used_page_count)*8/1024x768/1024x768  asUSED_GB,sum( Case  whenps.index_id<2                     ThenPs.in_row_data_page_count+Ps.lob_used_page_count+Ps.row_overflow_used_page_countElse 0 End        )*8/1024x768/1024x768  asDATA_USED_GB,sum( Case  whenps.index_id>=2                  ThenPs.in_row_data_page_count+Ps.lob_used_page_count+Ps.row_overflow_used_page_countElse 0 End        )*8/1024x768/1024x768  asINDEX_USED_GB fromsys.dm_db_partition_stats PSInner Joinsys.tables T onPs.object_id=T.object_idGroup  byT.object_id, T.nameOrder  byUsed_gbdesc

3.2 To see if the table and its index have been compressed

SelectP.PARTITION_ID,object_name(p.object_id) asObjectName, p.index_id, P.rows, p.data_compression, P.data_compression_desc, AU. Type, AU. Type_desc, Au.total_pages, Au.used_pages, Au.data_pages fromsys.partitions PInner Joinsys.allocation_units au onp.partition_id=au.container_idwhereP.object_id=object_id('[dbo]. [table_name]'N'U')

3.3, estimated storage space that can be saved by compression

execsys.sp_estimate_data_compression_savings@schema_name='dbo',                @object_name='table_name',                @index_id=1,                @partition_number=NULL,                @data_compression ='page'

3.4, data compression for table and its index
Rebuild,sql server will reallocate storage space for table and its index : rebuild will instead increase the storage space occupied by the database . After the data is compressed, the database files must be shrink in order to free up the storage space occupied by the database and to increase disk.

Alter Table [dbo]  with (data_compression=page)alterindex  index_name  on[dbo]with (data_compression= page)

4, increase the transaction log file

Reference: "The transaction log for database ' tempdb ' was full due to ' active_transaction '"

Appendix: "Log Reuse Waits explained:active_transaction"

SQL Server would return a log_reuse_wait_desc value of active_ TRANSACTION if it runs out of virtual log files Beca Use of an open transaction. Open transactions prevent virtual log file reuse, because the information in the "Log records for" transaction might be Required to execute a rollback operation.

To prevent the This log reuse wait type, make sure you transactions to being as short lived as possible and never requ IRE End user interaction while a transaction is open.

to resolve this wait, you have to commit or rollback all transactions. The safest strategy is to just wait until the transactions finish themselves. Well-designed transactions is usually short lived, but there is many reasons that can turn a normal transaction into a l OG running one. If you cannot afford-to-wait for a extra-long running transaction to finish, you might has to kill the its session. However, that would cause that transaction to be rolled back. Keep this on mind when designing your application and try to Keep all transactions as short as possible.

One common design mistake that can leads to very long running transactions are to require user interaction while the TRANSAC tion is open. If the person that started the transaction went to lunch and the system is waiting for a response, this transaction can Turn into a very-long-running transaction. During this time other transactions, if they is not blocked by this one, would eventually fill up the log and cause the LO g file to grow.

The transaction log for database ' XXXX ' was full due to ' active_transaction '