You can truncate only nonactive portion of the transaction log. If your transaction log is very big and full, and you want to truncate transaction log, try the following:

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 = 'c:\mssql7\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.