FAQ: truncate transaction log


	About Us
	SSWUG Articles
	Articles
	FAQ

	Administration
	Backup/Restore
	Connectivity
	Development
	General
	Installation
	OLAP
	Replication
	Transfer/move
	Trouble
	SQL 6.5

	Scripts
	Tips
	Test Exams
	Advertise
	Download
	History
	Search
	Traffic
	Related Links


Add to Favorites

Why cannot I truncate the transaction log?

Answer:

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. This is undocumented and not recommended way, so do not remember to make backup (see step 1).

Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2005 Bits on the Wire, Inc