Handling SQL Server Backup Log With Truncate_Only Or with No_Log

Introduction

All the changes made to the database are stored in the Transaction Log whereas; the data are stored in a separate file. For a database there is a physical transaction log file and a data file. A transaction log contains enough information for removing the changes made to the data file. You can see that, the log size increases as the database is managed and ultimately, contributes to the decreases of the disk space.The increase of size can be because the database backup is in FULL/BULK mode recovery. If proper action is not taken then, the disk space will continue to decrease.

For managing the transaction, the first thing that strikes our mind is to use “NO_LOG” or “TRUNCATE_ONLY” commands. And the regular backups of transaction log file can also help us to reduce the size of the transaction log. Let’s see, in detail about, what these commands do.

SQL Server Backup Log With Truncate_Only Or with No_Log

What does actually these commands do? Have you ever tried it and experienced its output? In this segment, you will come across the answers to all these. In general, if these commands are made to do then, issues can be caused. Usually, these commands are not recommended since it has got many drawbacks or consequences.

The commands allow to resets the data in the transaction log and thus, make the space available for reusing and stop the growth of the transaction log. The command should be performed only when the transaction log size is much large that, the shrinking is necessary otherwise your machine may end up in crashing.

Create a database ‘sample’ in your SQL Server database and follow these statements;

When you run these statements in 2005 SQL Server, you will find many error messages and the statements won’t be executed completely. The message may be like;

The issues say that, the transaction log has been truncated or the log is broken and ends up in the error messages as shown above.

When you run the above commands in 2000 Server, you will end up in:

Issues Related To Backup With TRUNCATE_ONLY or with NO_LOG Commands

The uses of these commands are not recommended since it destroys the database. All the backup data from the last backup will be lost and you may find the business related files are lost along with it.

We use Full Recovery Model for backup so that, restoring to the point of time is easy. But, the NO_LOG command nullifies this opportunity. And about the TRUNCATE_ONLY command,it is an unsafe command since it removes all the contents of your SQL Server without any backup of it. To be more precise, these commands either the NO_LOG or TRUNCATE_ONLY wear out the log chain and also losses the ability to restore to the early point in time.

Now, Microsoft has recommended to the 2005 and 2008 SQL Server users to change recovery model to SIMPLE instead of going for these commands. Now, when you use TRUNCATE_ONLY command in 2008 server, you will receive the following error message:

The administrator sometimes uses these commands before using the DBCC SHRINKFILE, for emptying the space in the drive. But,if you want; you can try to stop these commands from running. These issues are crucial for backup log with truncate_only or no_log commands.

How To Stop These Commands?

For stopping these commands, if you are the admin then, follow flags 3231 in SS2000 and SS2005. It will turn the commands to no-ops in the BULK/FULL Recovery Models and enables to clear the log file in the SIMPLE Model. This way preserves others from cut-shorting the log.

What Are The Alternatives Available For This?

One of the alternative ways for backup log truncate only is to try the SIMPLE recovery model during the backup keeping in mind that you will lose the ability to restore to the point-in-time.

Or if you want to keep the property of point in time restore then, you will have to follow the FULL/BULK recovery model by regularly backing your logs. If you find that the size of the log file is increasing then, you will have to find another space to store the backup. Buy external disk and store the backup of the transaction log files in that disk. But, you will have to regularly maintain the backup of the transaction log.

Conclusion

Transaction log files are of much important. You will see the changes made to the data file in the transaction log. According to the changes made, the size of the file too gets increase. Usually, no one cares the file until something worse happens. This is the main significance for SQL server backup log truncate only commands.

Without the backup of the transaction file one cannot restore to the point in time even if the data file is backup. The size of the transaction file increases if it is not backup properly. The backup can be done using NO_LOG or TRUNCATE_ONLY commands but, it is not as suitable.