Wednesday, December 31, 2008

Cleaning the Transaction Logs in SQL Server2005

To Back up the transaction log file :
BACKUP LOG TO DISK = ''
Eg.
BACKUP LOG TestDB TO DISK='C:\TestDB1.bak'

To Shrink the transaction log file:

DBCC SHRINKFILE (, ) WITH NO_INFOMSGS

How to clean the transaction Log in SQL Server

use master
go
dump transaction with no_log
go
use
go
DBCC SHRINKFILE (, 100) -- where 100 is the size you may want to shrink it to in MB, change it to your needs
go
-- then you can call to check that all went fine
dbcc checkdb()

(or)

To Truncate the log file:

Backup the database
Detach the database, either by using Enterprise Manager or by executing : *Sp_DetachDB [DBName]*
Delete the transaction log file. (or rename the file, just in case)
Re-attach the database again using: *Sp_AttachDB [DBName]*
When the database is attached, a new transaction log file is created.
To Shrink the log file:

Backup log [DBName] with No_Log
Shrink the database by either:

Using Enterprise manager :- Right click on the database, All tasks, Shrink database, Files, Select log file, OK.

Using T-SQL :- *Dbcc Shrinkfile ([Log_Logical_Name])*

You can find the logical name of the log file by running sp_helpdb or by looking in the properties of the database in Enterprise Manager.