Truncating the transaction log on SQL Server

Depending on the recovery model that you are using for your database, the size of your transaction log file may increase a lot.

If you would like to truncate and shrink the file, you can decide to use one of the follow options:

1. Make the full database backup.
2. Set the Truncate Log On Checkpoint database option and then run CHECKPOINT command from the Query Analyzer.
3. If the transaction log was not truncated, run the DUMP TRANSACTION command with NO_LOG parameter.
4. If the log was truncated, you can decrease the size of the log file by using the DBCC SHRINKFILE statement.
If the transaction log was not truncated, and the database have only one data file, detach the database by using the sp_detach_db stored procedure, then attach only the data file by using the sp_attach_single_file_db stored procedure. The transaction log will be recreated automatically with the small size. This is the example to detach/attach the Test database:

USE master
EXEC sp_detach_db 'Test', 'true'
EXEC sp_attach_single_file_db @dbname = 'Test', @physname = 'd:\data\Test_Data.MDF'

You can also stop the MSSQLServer service, drop the transaction log file, start the MSSQLServer service. The transaction log will be recreated automatically with the small size.

This is undocumented and not recommended way, so do not forget to make backup before.

Hope it helps!