6

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.

Community
  • 1
Ketan
  • 497
  • 3
  • 6
  • 14

3 Answers3

4

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.

David Browne - Microsoft
  • 49,000
  • 3
  • 53
  • 102
2

For my database, this was due to the indirect checkpoints feature. If your application does not have very many dirty pages, but a lot of transaction log, the log will just keep growing. For example Citrix will update the same column over and over. This does not increase the dirty page count. Indirect checkpoints rely on the number of dirty pages to know when to run a checkpoint.

Turn off the indirect checkpoint feature:

ALTER DATABASE DBName SET TARGET_RECOVERY_TIME = 0 SECONDS WITH NO_WAIT

This may not be the reason, but that is what caused it for my database.

Paul White
  • 94,921
  • 30
  • 437
  • 687
PianoDan
  • 143
  • 1
  • 1
  • 8
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).

Josh Darnell
  • 30,133
  • 5
  • 70
  • 124