0

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

2 Answers 2

0

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://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-2017

0

First that percentage growth is a killer. Try to replace that with tested number value rather than auto growing in percentages.

Secondly, you should check how often are you taking the log backups. If not, you should consider taking at priority as you said recovery mode is Full.

Coming to why it bloated, there can be various reasons. If you are tracking the queries in past via some monitoring tool, you can get more accurate reasons on what and why.

For future, you can create extended event to track the log and data (optional) file growths to answer this question.

4
  • as mentioned, i have enough harddisk storage and very big allowed transaction log space to grow. How can the transaction log unable to grow? i assumed it has enough storage to grow
    – SKLTFZ
    Mar 28, 2019 at 6:14
  • Probably that the thread that grow the file couldn't grow quickly enough for the thread that need this space. I.e., error message. What you see is not uncommon. Reasonable grow size, turn on Instant File Initialization (if your security permits) and try to pre-allocate when possible. Mar 28, 2019 at 7:56
  • @TiborKaraszi do you have any documentation for that? Why wouldn't that just end up in a Server Wait being generated and the thread waiting for the file expansion to complete? Mar 28, 2019 at 9:53
  • No, just speculation from my side. But since we see errors every now and then even though the files should have been able to grow then there evidently is something in SQL Server that doesn't "catch up". Mar 28, 2019 at 12:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.