How to shrink LOG file in MS-SQL Server 2005/2008

Hi,

There was a problem with Log file was bigger that 40Gb  in my DB.

With these steps I managed to shrink a file:


USE [DBNAME]
GO
CHECKPOINT -- Write checkpoint to the log file, and force SQL to write all dirty pages to the database.
GO
ALTER DATABASE [DBNAME] SET RECOVERY SIMPLE -- Set database to Simple recovery, essentially truncating all the non-active part of the transaction log file.
GO
ALTER DATABASE [DBNAME] SET OFFLINE WITH ROLLBACK IMMEDIATE -- Kill all active transactions/connections rolling back any changes that are outstanding.
GO
ALTER DATABASE [DBNAME] SET ONLINE -- Bring database back online (should have no active connection now).
GO
DBCC SHRINKFILE('LOGICAL_FILE_NAME',0) -- Since we have killed all active transactions, and we have set database to simple recovery mode; all VLF should be in inactive state now; and you should be able to shrink the file.
GO 


Of course you should change backup recovery mode to FULL after this steps.