THIS WAY HAS WORKED WELL FOR ME:

The easiest way I have found to immediatley shrink the log file is to detach the database, rename the .LDF file then reattach the .MDF file. In SQL Server 2005 you'll need to remove the missing .LDF file from the list before attaching, and one will be created for you.

I DID NOT GET THIS ROUTINE BELOW TO WORK WITH SQL SERVER 2005:

From the April 2001 edition of SQL Server Magazine:

Shrinking Log Files Immediately

You can use the DBCC Shrinkfile statement in SQL Server 7.0 to shrink the size of a specified data file or log file for a related database. But SQL Server professionals often ask me why the DBCC Shrinkfile command doesn't shrink the size of their transaction logs immediately and how to make their transaction logs smaller.

SQL Server 7.0 doesn't immediately shrink log files when you issue the DBCC Shrinkfile command. The DBCC Shrinkfile operation occurs only at checkpoints or transaction log backups. SQL Server segments each physical log file internally into a number of virtual log files (VLFs), which make up the transaction log. SQL Server marks the VLFs as truncateable either after SQL Server has backed them up or at checkpoints. At any given time, you might have VLFs with free or reusable space at the beginning, middle, and end of the log. Only when the VLFs that SQL Server marked as truncateable are at the end of the log file can the DBCC Shrinkfile operation remove the VLFs and shrink the log file. Because SQL Server can shrink a log file only to a virtual-log-file boundary, you can't shrink a log file to a size smaller than the size of a virtual log file—even if you aren't using the log file. (For more information about virtual log files, see SQL Server Books Online—BOL.)

Listing 1 shows a script that shrinks the log file immediately after you stop running the script. The script first marks a shrinkpoint, which tells the DBCC Shrinkfile (or the DBCC Shrinkdatabase) command where to shrink the log file to. The script then forces truncateable VLFs to the end of the log file and issues a BACKUP command to truncate the log. In my experience, you need to run the script for 3 to 4 minutes before stopping it manually.

—Simon Su  yqsu@microsoft.com

Listing 1: Script to Shrink a Log File

/* Run "SELECT fileid, name,filename FROM <db_name>..sysfiles" to get
the fileid you want to shrink. */

USE <db_name>
GO
DBCC shrinkfile(<fileid>,notruncate)
DBCC shrinkfile(<fileid>,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 <db_name> with truncate_only
END
GO