Sql-server – The actual cause of transaction log full under below scenario

sql servertransaction-log

in one of the instance, it shows

The transaction log for database 'DB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

The instance has at least 100g harddisk empty storage and the transaction log allowed 2,097,152MB (2T) to grow, growth rate at 10%.

The database recovery mode is FULL (at least it displays FULL in the SSMS)

In this case, what can cause the exception of full transaction log?

When the transaction log have enough physical storage to grow and the log size is not bigger than the upper bound

Best Answer

First of all do what the message says and run

SELECT log_reuse_wait_desc FROM sys.databases WHERE name = 'your db name'

I'd also check sys.database_files in your database to confirm the actual settings for the transaction log file.

Once you have the reuse identifier then check it against the values at https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-2017