This article describes how to truncate transaction log file in SQL Server 2008 and 2005.
Truncating Transaction Log in SQL Server 2005
In SQL Server 2005 you need to run these commands:
[codesyntax lang=”sql”]
dbcc shrinkfile(dbname_log,1) backup log dbname with truncate_only dbcc shrinkfile(dbname_log,1) [/codesyntax]
Here dbname_log is a logical file name of the transaction log file. You can see the list of files with their sizes by using this command:
[codesyntax lang=”sql”]
SELECT * from sys.database_files
[/codesyntax]
The command “Backup log .. with truncate_only” removes an inactive part of the log. The size of the transaction log file on disk remains the same after running this command.
Shrinkfile command releases the unused space to the OS.
Truncating Transaction Log in SQL Server 2008
You need to truncate the transaction log file when you want to free an additional space on a hard disk or truncate the log file after backups.
If you use SQL Server 2008 Express Edition then you have a 10GB limit for a database size including the transaction log files. So if you don’t want to go beyond the limit you should truncate log file.
If the transaction log file is full you will get this error:
The transaction log for database ‘_dbname_’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
The commands shown for SQL Server 2005 do not work. BACKUP LOG with the option ‘TRUNCATE_ONLY’ gives an error in SQL Server 2008.
‘TRUNCATE_ONLY’ is not a recognized BACKUP option.
Backup log with truncate_only is no longer supported in SQL 2008.
The only way that works is to change the recovery model to SIMPLE, then shrink the file, and, finally, return the recovery model back to the original state (usually, FULL):
[codesyntax lang=”sql”]
alter database dbname set recovery simple go DBCC SHRINKFILE (N'dbname_log', 1) go checkpoint go alter database dbname set recovery full go
[/codesyntax]
Check the result:
SELECT * from sys.database_files
Read more:
Read this article in TechNet about how How Log Truncation Works .