2

We have a database hosted on azure. It is part of an azure elastic pool. From yesterday all our database operation are consistently failing with following error.

The transaction log for database is full due to 'OLDEST_PAGE'

We checked for all active transactions on database but there are no active transactions currently but still log file is taking 100% of space.

db_log_file

I have gone through following documentation pages, but I was not able to understand the issue completely:

Can anyone please help me understand this issue?



Update

When we moved this DB to new pool then log size reduced automatically. We are not experiencing any issue after move.

  • 59MB is tiny...is the file capped to that size? – Kevin3NF May 4 '18 at 11:51
  • DB is hosted on azure. We havent put any specific size cap on log file. – Ketan May 4 '18 at 12:05
3

This is not something you should ever see on an Azure SQL Database. You should engage support on this.

There are a couple things you could do yourself, if you want. First just try to CHECKPOINT the database. Second if you are sure you have no active transactions, change the database SLO. Taking it out of the Elastic Pool, then putting it back in.

  • FWIW - We have been getting burned by this for a week all over our 1000 eDTU Premium cluster. Support is entirely unable to help. – Andy Davis Sep 20 '18 at 12:38
  • Do you have a case number? – David Browne - Microsoft Sep 20 '18 at 17:15
  • Yes. Would you like it? If so, what is the best way to get it to you? – Andy Davis Sep 20 '18 at 21:05
1

Note: I wrote this answer before I realized this was on Azure SQL DB (David's answer seems to indicate this is legit buggy behavior in Azure).

Leaving here as the OP was also looking for an explanation about what that error meant, and maybe it will be helpful for others in an on-prem setup with this error.


Your transaction log is full. Looking at the screenshot you shared, it's only 59 MB. Which is pretty small. One would think it would just keep growing to accommodate more transactions. But it can't for some reason.

SQL Server wants to start re-using the log file. But you've got that pesky OLDEST_PAGE thing happening. This means that there are modified date pages in memory that haven't been persisted to the data file on disk, and thus SQL Server can't start re-using the part of the transaction log that documents those potentially-unpersisted transactions.

A temporary fix would be to run the CHECKPOINT command manually on the server, to try and force those buffered pages to be flushed to disk. You may have to run the command more than once, but this will allow that transaction log file to start being reused.

The bigger problem is with your transaction log. You should run this query:

select max_size, growth from sys.master_files where [name] = 'YourLogFileName';

And then:

Check max_size to see if there is an explicit maximum preventing your log file from growing

If this is the case, set max_size to a larger number to accommodate your actual transactional load. The number for this will depend on your usual number of transactions, and what recovery model you're using.

Check growth to see if autogrowth has been disabled (growth = 0)

If it is disabled, you could enable it.

If you can't do that, and you're in FULL recovery model, you could schedule more frequent log backups.

If you can't do that, and you're in SIMPLE recovery model, you'll need to increase the size of the log file manually until it's large enough to handle the transactions you have between automatic or indirect checkpoints.

It's possible, maybe, that the disk of your Azure storage is full and that's what is preventing the file from growing (although I'd expect different error messages).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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