Today I witnessed yet another user who was dealing with BACKUP LOG [database] WITH TRUNCATE_ONLY.

This is one of the features which got discontinued from SQL Server 2008 and above,and this was one of the best decisions Microsoft had taken after SQL Community experts gave valid reasons for discontinuing it.

I decided to write this blog post to explain why this feature is an evil and force SQL Server 2000(Yes,you heard it right! There are many companies which still uses it) and SQL Server 2005 users not to use it.

When you do a Truncate_Only it simply means that the log backup chain is broken and you are loosing your ability of doing a point in time restore.

This is a worst case situation if you are hit with a disaster.

Lets do a quick demo to demonstrate this –

[Using SQL Server 2005 with SP4 for the demo]

1. I have created a test database named as Test_Truncate

CREATE DATABASE [Test_Truncate]

2. Created a table and inserted one record

USE [Test_Truncate]
CREATE TABLE dbo.Test_Truncate (ID INT IDENTITY (100,1), Description VARCHAR (50))
USE [Test_Truncate]
INSERT INTO dbo.Test_Truncate VALUES('Mercedes')

3.  Now we will proceed and take a backup of this database

BACKUP DATABASE Test_Truncate TO DISK='D:\Test_Truncate_1st_Fullbackup.bak'

4. Insert one more record

USE [Test_Truncate]
INSERT INTO dbo.Test_Truncate VALUES('BMW')

5. Now we will proceed and take a transaction log backup

BACKUP LOG Test_Truncate
TO DISK='D:\Test_Truncate_1st_Tranlog.trn'

6.Insert 3 more records

USE [Test_Truncate]
INSERT INTO dbo.Test_Truncate VALUES('Mini Cooper')
GO
INSERT INTO dbo.Test_Truncate VALUES('Ford')
GO
INSERT INTO dbo.Test_Truncate VALUES('GMC')

7. Now we will do a BACKUP lOG WITH TRUCATE_ONLY

Command(s) completed successfully.

8. Insert 3 more records

USE [Test_Truncate]
INSERT INTO dbo.Test_Truncate VALUES('Impala')
GO
INSERT INTO dbo.Test_Truncate VALUES('Cruz')
GO
INSERT INTO dbo.Test_Truncate VALUES('Nissan')

9. Now we try to take a transaction log backup again

BACKUP LOG Test_Truncate
TO DISK='D:\Test_Truncate_2nd_Tranlog.trn'

As soon as we execute this tran_log backup command we will get the error

Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

This means that we truncated the transaction log prior to this backup and broke the log chain.

If you dont take a full backup immediately after the error and continue with normal log backups,then you dont have a recovery chain to recover the database in the event of a failure.

I have observed a different behavior in SQL Server 2000(Yes! Again I said 2000) were transaction log backups are created after Truncate_Only,however there are not valid for a restore and you will end up getting error message like this if you try to restore them after a full backup restore.

Server: Msg 4330, Level 16, State 4, Line 1
The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

SQL Server 2005 is much better that it doesn’t allow any transaction_log backups soon after Truncate_Only.

Conclusion –

You have two options to deal with log filling issues

1 . Take a regular tran_log backup if you are running on FULL RECOVERY MODEL

2. Put your databases to Simple RECOVERY MODEL if you dont need point in time recovery.

Thanks for reading.