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.
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
CREATE TABLE t1 ( id int, amount int )
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
INSERT INTO dbo.t1 ( id, Amount )
VALUES ( @i, @j + @i )
SET @i = @i + 1
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.
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'
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.