The transaction log for database is full due to ‘OLDEST_PAGE’

azureazure-sql-databasetransaction-log

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.

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.

Best Answer

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.

Related Solutions

Sql-server – Transaction Log and mirroring – looking for dumbest explanation possible

The transaction log is a important method to restore your database in a specific time. If you have a large database > 500 GB and if you must restore your database from a full backup, this will cost very much time. Also if you full backup your database everytime, think about how long this backup could take.

A very easy concept for SQL Server can be: Set Recover Model of your Database Full

Create a Maintenance Plan(1) in SQL Server:

Do FullBackup every Week maybe in D:\yourbackup\FullDBBackup.bak
Do Differential Backup every two days in D:\yourbackup\DiffBackup.bak
Do every 2 houers Backup your Transaction log in D:\Yourbackup\Tranlogbackup.trn

Create a Maintenance Plan(2) in SQL Server:

Delete all older Files 8 days from D:\yourBackup*.bak
Delete all older Files 3 Days from D:\yourBackup*.trn

In this case, you are able to recover your Database in a specifc time, very fast very easy. SQL Server will automaticly manage your "Backup" files, older files will be deleted after your specific time range.

I would suggest that you read about SQL Server Transaction Log here:

http://www.sqlservercentral.com/articles/Design+and+Theory/63350/

For using the Maintenance Plans in SQL Server just ask BING / google :D

you should build a small test db and test this before you go in production

Db2 – Transaction(s) still alive: How to find the cause

If you're using a recent version of DB2 on Linux, Unix, or Windows (LUW), you can use the db2pd tool to view all current transactions and how much log space they use (the LogSpace column):

$ db2pd -d tst1 -transactions

Database Member 0 -- Database TST1 -- Active -- Up 3 days 00:34:56 -- Date 2013-10-03-10.19.08.041274

Transactions:
Address            AppHandl [nod-index] TranHdl    Locks      State   Tflag      Tflag2     Firstlsn           Lastlsn            Firstlso             Lastlso              SpaceReserved   LogSpace        TID            AxRegCnt   GXID     ClientUserID                   ClientWrkstnName               ClientApplName                 ClientAccntng                  
...
0x00007F05AE48CA80 17       [000-00017] 686        5          WRITE   0x00000000 0x00000000 0x000000000007BF73 0x000000000007BF75 103504642            103504834            328             616             0x00000000F64C 1          0        n/a                            n/a                            n/a                            n/a

Best Answer

Related Solutions

Sql-server – Transaction Log and mirroring – looking for dumbest explanation possible

Db2 – Transaction(s) still alive: How to find the cause

Related Question