How to immediately shrink the SQL Server log files

Have you ever wanted to shrink your log files but couldn't do it no matter how hard you try? While I and the majority of SQL Server DBAs would not recommend shrinking the transaction log (or any data file in your database), you'll end up doing it one way or another due to emergency situations. Here's what you'll probably end up doing as defined in this Microsoft KB article:

• Backup the transaction log. This will truncate the log
• Shrink the log file

Pretty simple, right? Well, there are times when this might not work because SQL Server does not shrink the log immediately. The DBCC SHRINKFILE operation occurs only at checkpoints or transaction log backups. SQL Server divides each physical log file internally into a number of virtual log files (VLFs), which make up the transaction log. This MSDN article describes virtual log files in SQL Server. SQL Server MVP Tibor Karaszi highlights why you would not want to shrink your log files. This blog by Johnny Hughes has a script that lets you do this task.

USE databaseName
GO

DBCC shrinkfile(<file_id>,NOTRUNCATE)
DBCC shrinkfile(<file_id>TRUNCATEONLY)

CREATE TABLE t1 (CHAR1 CHAR(4000))
GO

DECLARE @i INT
SELECT @i = 0
WHILE (1 = 1)

BEGIN
WHILE (@i < 100)
BEGIN
INSERT INTO t1 VALUES ('a')
SELECT @i = @i +1
END

TRUNCATE TABLE t1

BACKUP LOG databaseName WITH TRUNCATE_ONLY
END