Looking into Transaction Log size and clearing and database backups

In this post I would like to talk about the size of the transaction log and under what circumstances it clears in relation to recovery models and database backup types (Full, Differential, Log). I will also give a brief overview of recovery models, database backup types.

When I usually write a blog post that is often due to questions I receive in my mailbox or real problems my clients face and I have to resolve. A few days ago I got an email from a friend of mine. He was telling me that his website was down and was not functioning. He said the error he was seeing in was

“The transaction log for database ‘mydb’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases”

It is obvious that there is a problem with the transaction log and that is why his site is not functioning.I will also provide hands-on examples when needed.

Before we proceed I would like to explain the various recovery models.We have 3 recovery models available in SQL Server

• Simple
• Full
• Bulk-Logged

In the Simple Recovery Model every time I have a checkpoint operation or the .ldf file is full, the .ldf file is automatically truncated.In this model the transaction log is not backed up.You can not have point in time data recovery with this model.Data is recoverable only to the most recent backup.

The Simple Recovery Model  is only suitable for small databases and databases with few transactions.We could use this recovery model if we want to have a small transaction log and for databases that are read-only or in development stage.

The Full Recovery Model is the default recovery model.In this model the transaction log grows rapidly since all database transactions are logged in at a very detailed level.If I have a catastrophe in my database I can rollback to the time where the catastrophe occurred.This recovery model is suitable for databases with thousands of transactions per day and where no loss of data is tolerated.

The Bulk Recovery Model works almost identically with the Full Recovery Model.It logs everything in extents=8pages and all bulk tasks (bcp,bulk insert) are logged minimally.We can go back to the time of failure but it will take more time to get the database back on-line.This model would be very suitable for databases with large bulk tasks.The disadvantage of this model is that you cannot go back at a specific point in time.

Having looked into the recovery models,let’s have a look on the various SQL Server backup types.

Let’s see what happens when we take a full database backup.The first thing to say is that you need always a full database backup.This is the base for all subsequent backups. You cannot have a differential backup,transaction log backup if you do not have at least one full backup.With a full database backup you backup all your data.You can have a full backup with all the recovery models that you choose to have for your database.Every full backup I perform in a database contains in it all the data previously fully backed up.
The backup operation is an online operation and the first thing it happens is to log that a backup was started. Then we write the data in the backup file.Then we do log any changes that occur as we backup.Finally the backing up of the data is finished and we log that fact.If you’re performing only full backups, you might lose some data in the event of a system crash—specifically, any changes made since the last full backup.

A differential backup is cumulative backup. It includes all data and structures that have changed since the last full backup, regardless of when that last full backup was made, or how many previous differential backups have been run.You can have a differential backup with all the recovery models.

The Transaction Log backup only works for full or bulk-logged recovery models.Each T-Log backup contains all the transactions that are in my database since the last T-Log backup.Always perform a full back up before you take a T-log backup.The time taken for a T-log backup is little and there is no performance penalty.An important thing to remember is that for every t-log backup I need the previous backup to have a restore.

Let me come back to the original problem which is that my friend’s website run out of transaction log space.
This happens when the transaction log fills up to the point where it has to grow but either autogrow is not turned on or the volume on which the transaction log is placed has no more space for the file to grow.

That is profound. But what has caused the transaction log to fill up in the first place? It could be a number of different things.

The two most common ones I’ve seen are:

1. The database is in full recovery model and subsequent full database backups were taken but no log backups have been taken.

This commonly happens when an application goes into production and someone decides to take a backup, without realizing the consequences. Log truncation is not automatic in such a case – a log backup is required to allow the log to truncate.

2. The database is in any recovery mode but there’s a very long-running, uncommitted transaction that prevents log truncation.

In this case I was called to examine, the problem was that the database was in full recovery mode, subsequent full database backups were taken but no log backups have been taken.

Some people believe that a full database backup in the full or bulk logged will clear/truncate the transaction log.

Well, these people are wrong. Simple as that. They are wrong.This is not true. Only a transaction log backup will clear the log under these recovery models.

On the other hand, in the SIMPLE recovery model, a checkpoint clears the transaction log. I see people that have critical data in their databases and need to restore it (in case of a catastrophe) to the point of failure, to use Simple recovery model in their databases because the transaction log is getting too large. So in order to keep it small, they switch recovery models. This is so wrong!!!! In case of a failure these people will lose data they cannot afford to lose.

Let me go on with the actual example

I will be using SQL Server 2012 Εnterprise edition in this post but feel free to use SQL Server 2008/R2 versions and any other edition you might have installed (Standard, Express e.t.c).

1) Launch SSMS and connect to the local (or any other instance) of SQL Server

2) I will be looking at the column log_reuse_wait_desc of the sys.databases

3) In a new query window in SSMS , type (or copy paste) the following t-sql script and then execute it (substitute database football with any other you have attached in the instance of SQL Server.)

SELECT  log_reuse_wait_desc
FROM    sys.databases
WHERE   name = 'football'

In my case I receive “Nothing” and that is why you will receive yourself if you execute the T-SQL script above but in my friend’s case when I connected to the SQL hosting the database I go the following:

Log Backup

That is all I needed to know. SQL Server tells me what the problem is.The database was in Full recovery model, only full backups were taken and no log backups have been taken.

4) Now I will show you why a full database backup does not clear the transaction log

Type (copy and paste) the following T-SQL script but do not execute it yet.

USE MASTER;
GO

CREATE DATABASE [Football] ON  PRIMARY
( NAME = N'Football_Data', FILENAME = N'D:\DATA\Football_Data.mdf' , SIZE = 80MB , MAXSIZE = UNLIMITED, FILEGROWTH = 20MB )
LOG ON
( NAME = N'Football_Log', FILENAME = N'D:\DATA\Football_log.ldf' , SIZE = 20MB , MAXSIZE = UNLIMITED, FILEGROWTH = 5MB)
GO

USE FOOTBALL;
GO

CREATE TABLE Footballer
(
FootballerID int identity(1,1),
Footballer_No varchar(20),
Footballer_lastname nvarchar(50),
Footballer_firstname nvarchar(50),
Footballer_middlename nvarchar(50)
)
GO

CREATE UNIQUE CLUSTERED INDEX cx_Foo_IX ON Footballer (FootballerID)
GO

declare @CTR INT
declare @ctrstr varchar(7)
select @ctr=0
WHILE @CTR<4000
BEGIN
select @ctr=@ctr+1
select @ctrstr=Str(@ctr)
insert footballer(Footballer_No ,Footballer_lastname,Footballer_firstname, Footballer_middlename) values('F_No'+@ctrstr,'Footlname'+@ctrstr,'Footfname'+@ctrstr,'Footmname'+@ctrstr)
END

BACKUP DATABASE [Football] TO  DISK = N'D:\DATA\backup\football_full.bak' WITH INIT,STATS;
GO

BACKUP LOG [Football] TO  DISK = N'D:\DATA\backup\football_log.bak' WITH INIT,STATS;
GO

ALTER INDEX cx_Foo_IX ON Footballer REBUILD;
GO

BACKUP LOG [Football] TO  DISK = N'D:\DATA\backup\football1_log.bak' WITH INIT,STATS;
GO

ALTER INDEX cx_Foo_IX ON Footballer REBUILD;
GO

BACKUP DATABASE [Football] TO  DISK = N'D:\DATA\backup\football1_full.bak' WITH INIT,STATS;
GO

BACKUP LOG [Football] TO  DISK = N'D:\DATA\backup\football2_log.bak' WITH INIT,STATS;
GO

Let me explain what I am doing.

In lines 1-8 I create a  sample database Football.

In lines 10-24, I create a table, Footballer and a unique clustered index on column FootballerID.

In lines 26-34 I populate the Footballer table.

In lines 36-37 I take full database backup.

In lines 39-40 I take log database backup.

In lines 42-43 I rebuild the clustered index.

In lines 46-47 I take another log backup.

In lines 49-50 I rebuild the clustered index.

In lines 52-53 I take another full database backup. If a database backup clears the log, the next transaction log , should be small.

In lines 55-56 I take another log backup.

Let’s now compare the size of the last two transaction logs. Have a look at the picture below

As you can see the size of the transaction logs is more or less the same.So the last transactional log is not smaller,thus the full database backup did not clear the log.

The way I solved my friend’s problem was to free disk space so that the log can automatically grow and then take a full backup following a log backup.

Then I explained him the importance of log backups in log truncation and size management. He has incorporated log backups in his database backup strategy.

Hope it helps!!!!