SQL SERVER – Shrinking Truncate Log File – Log Full

Few Examples how to shrink the transaction logfile in SQLserver:

Shrink a datafile to 64 Mb:

DBCC SHRINKFILE (MyDataFile01, 64);

Shrink a Log file to 8 GiB (8192 MiB):

USE MyDatabase;
GO
DBCC SHRINKFILE(MyDatabase_Log, 8192)
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE(MyDatabase_Log, 8192)

Afterwords, perform a full backup of the database.
To make the file as small as possible you can specify 1 for 1 Mb, or just leave out the target_size completely, be aware that doing this will slow down the system a little as the system will just have to grow the log file again as soon as another transaction is started.

In SQL Server 2008 the procedure is slightly different, the database must first be set to Simple recovery mode, then shrink the file, then restore FULL recovery mode:

ALTER DATABASE MyDatabase SET RECOVERY SIMPLE
go
DBCC SHRINKFILE(MyDatabase_log)
go
EXEC sp_helpdb MyDatabase
go
ALTER DATABASE MyDatabase SET RECOVERY FULL
go 

===================================================================

 Following code always shrinks the Truncated Log File to minimum size possible.

USE DatabaseName

GO

DBCC SHRINKFILE(<TransactionLogName>, 1)

BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

DBCC SHRINKFILE(<TransactionLogName>, 1)

GO