I haven’t written a post for a TSQL Tuesday for a little while and I know that I’m late getting this one out too. This month’s party is being hosted by Sankar Reddy This topic  I think, maybe expect, really I know, that this topic has been covered already, I know that the Steve Jones has posted on this misconception already today and Paul Randal (Blog | Twitter) in his misconceptions series back in April covered this too much better than I have here so go and check out Paul’s post . The misconception I will look at here is - SQL Server will truncate the the transaction log after a full backup when the database is in full recovery mode. After my blog post around recovery models earlier today I think this maybe quite apt for me.

The misconception I am going to talk about here is SQL Server truncates the log of a database in full recovery mode when a full backup is taken. It doesn’t.

This one of the biggest problems I come across, a database in full recovery mode and regular full backups being taken but  no transaction log backups being taken. The log grows and grows and eventually fills up all drive space.

I tested this on a SQL Server 2000 instance earlier today and it holds true in that version too. SQL Server does not truncate the log when a full backup of a database is taken in full recovery mode, although I believe in versions gone by it may have been the case that a full backup truncated the log. If you know the exact version please leave a comment.

The Proof

I will create a database for the purpose of this test called demodb and then create a table in that database called t1 that will hold some data.

CREATE DATABASE demodb
GO

CREATE  TABLE t1 ( id int, amount int )
go 

Firstly I will take a full database backup, the reason for backup is to ensures the database is in Full recovery mode and not in psuedo-simple mode.

BACKUP database demodb to disk = 'C:\BACKUP\demodb.bak'

If I then run a DBCC LOGINFO we can see that 1 of the 2 virtual log files (VLFs) are are active, they have a status of 2. I will cover VLF’s and the circular nature of the transaction log in a later posts, but for now DBCC LOGINFO shows some information on VLFs. a status of 2 means the VLF  is active, a status of 0 means that it is inactive or has been been truncated and can be used again.

I will then run some inserts on my table, all of which will be logged in the transaction log:

DECLARE @i INT
SET @i = 0

DECLARE @j INT
SET @j = 100000

WHILE @i < 10000

BEGIN
        INSERT  INTO dbo.t1 ( id, Amount )
VALUES  ( @i, @j + @i )
SET @i = @i + 1
END
SELECT  GETDATE()

Running the DBCC LOGINFO command again,  you can see from the below diagram the log has grown and the number of active VLF’s has increased, I now have 103 VLF’s in my log and 102 are active, this means they cannot be re-used until they are marked as inactive and truncated.

I will then run another full backup of my database, to prove that a full backup of a database in full recovery mode does not truncate the log.

backup database demodb to disk = 'C:\BACKUP\demodb2.bak'

Running DBCC LOG info shows that the log has not been truncated by the full backup, the same number of VLFs are still active with a status of 2.

dbcc loginfo

The following results show that we still have 103 VLFs with 102 with a status of 2 – active.

If we then run a LOG backup and then run the DBCC LOGINFO command we can see that the log has been truncated and more of the VLF’s have been marked as inactive with a status of 0 and can now be reused.

backup log demodb to disk = 'C:\BACKUP\demodblog.trn'
GO
dbcc loginfo

You will see that I still have 103 VLFs (more on that later) but now only one of those is active with a status of 2. The rest have been truncated and can be reused in the log. I hope that is helpful. I have rushed this post to get it out in time, I didn’t realise it was that time again until a couple of hours ago, if you find any inaccuracies in the post please let me know in the comments.