transaction log

In Microsoft SQL server 2000 and 2005, as part of the ‘BACKUP LOG’ command, there was an option to truncate the log file, without storing a backup to file. In Microsoft SQL Server 2008, this option has been removed, and you can now no longer use the ‘TRUNCATE_ONLY’ option when performing a transaction log backup.
The truncate only option is used to truncate the log file. This is generally done so you can then shrink the transaction log file, and recover the disk space back to the file system.
The error message that occurs when you try the ‘TRUNCATE_ONLY’ option in Microsoft SQL 2008 is as follows:

Msg 155, Level 15, State 1, Line 1
'TRUNCATE_ONLY' is not a recognized BACKUP option.

To truncate the transaction log file in Microsoft SQL Server 2008, without making an actual transaction log backup (possibly due to free space limitations), you need to change the recovery model of the database to “Simple”, and then change it back to “Full” (or “Bulked Logged” if that’s what it was previously).
In the process of making the database recovery model “Simple”, it truncates the transaction log file, as it is not used with the simple recovery model.
This can be done manually via the Microsoft SQL Server Management Studio tool, or it can be done via SQL statements, and scripted if needed.
SQL statements to change the database model of a specific database:

ALTER DATABASE [<database_name>] SET RECOVERY SIMPLE
ALTER DATABASE [<database_name>] SET RECOVERY FULL

After doing the above, you can shrink the transaction log file using the following command:

DBCC SHRINKFILE(<log_name>)

For more information on shrinking the transaction log file, please check out the “How to shrink a Microsoft SQL transaction log file” article.

The transaction log file contains a record of every transaction against the database, since the last transaction log backup. All changes in the database are first saved to the transaction log, before being updated in the main data file.
The transaction log file can allow a database administrator to recover a database back to a specific point in time, rather than simply back to the last full backup. If you find there was some sort of database corruption at 4:30pm, you could recover the database back to 4:29pm using the transaction log file.
Because of these abilities, the transaction log file can actually grow over time, and become excessivly big, and potentially filling up the hard disk space on the machine that Microsoft SQL is running on.
Shrinking the transaction log file required two parts. First of all you need to truncate the log file itself, and then secondly you need to shrink the file itself. The truncate part clears the contents of the file, but it doesn’t actually reduce the file size of the transaction log on the file system.
To truncate/clear out the transaction log, you need to perform a transaction log backup. If for some reason you are not able to do a transaction log backup (usually due to the size/disk space restrictions), you can perform a “TRUNCATE_ONLY” backup, which just clears/truncates the transaction log without creating a backup file.
To do a “truncate only” backup, you would run the following query:

BACKUP LOG [<database_name>] WITH TRUNCATE_ONLY

(Unfortunatly, since Microsoft SQL Server 2008, the TRUNCATE_ONLY options has been deprecated. Instead you need to change the database recovery model to “Simple”, and then set it back to “Full”. This has the same effect as a “TRUNCATE_ONLY” transaction log backup. For more details, check the “‘TRUNCATE_ONLY’ is not a recognized BACKUP option in SQL 2008” article).
Once that has been done, you can actually shrink the transaction log file, which will reclaim the free space from within the file itself, and let the filesystem use it again. This will typically shrink it back down to about 1MB in size.
Run the following script to shrink the transaction log file:

DBCC SHRINKFILE(<log_name>)

Note: you can run “SELECT * FROM SysFiles” to determine what the log name should be. This will be in the “name” column. It has typically got a _LOG tag appended to the name.
Or, for a more automated approach, this will automatically get the log file details, and shrink it (Make sure you have truncated the log file first).

DECLARE @LogFile varchar(256)
SELECT @LogFile=name from Sysfiles WHERE UPPER(RIGHT(filename, 4)) = '.LDF'
DBCC SHRINKFILE(@LogFile)

For more information on what the transaction log file is, please read the “What is the SQL transaction log file?” article.

A Microsoft SQL database consists of at least two files, a data file (.mdf) and a transaction log file (.ldf). The data file contains all the data for the database, as you would expect. The transaction log file contains a record of every transaction against the database, since the last transaction log backup. All changes in the database are first saved to the transaction log, before being updated in the main data file.
The transaction log file can allow a database administrator to recover a database back to a specific point in time, rather than simply back to the last full backup. If you find there was some sort of database corruption at 4:30pm, you could recover the database back to 4:29pm using the transaction log file.
It is generally good practice to store the data and log files on different physical drives, or different raid arrays, providing both speed advantages, and protection from hard drive crashes. Keep in mind not to use RAID 0. While RAID0 provides some speed advantages, it provides no redundancy in the event of a drive failure.
The transaction log file can be backed up independently of the main database backup if required.
If you want to be able to take full advantage of transaction logging, you will need set the recovery model option for the database to “Full”. You could also have the recovery model set to “Bulk-Logged”, however any bulk operations will not be logged. If the database is set to “Simple”, you can only recovery from a previous full backup.
For production environments, it is recommented to use the “Full” recovery model.