SQL Server 2005 transaction log is always too big

Question

Hi, I've read other posts and have done hours of research but am still none the wiser. I have a database that is 65 gig in the data file, and currently 230 gig in the log file. I'm trying to redesign the database so it is more efficient but when making schema changes, the log file tries to grow too large and the windows whinges that it's out of disk space.

I've tried to shrink the file and the lowest it goes is ~220 gig. using DBCC OPENTRAN I can see there are no active transactions. using select * FROM sys.dm_tran_database_transactions I can see that there is nothing interesting going on.

My interpretation of what I have read is that the log file should only be large if there is an active transaction and once all transactions are committed, the file should be able to be shrunk to theoretically something very small. correct?

I've tried backup log with truncate_only followed by dbcc shrinkfile (dbname, 2)

What can I do to shrink this file to something more manageable?

Answer 1

Have you read Kimberly Tripp's article? (Pretty much the canonical reference on the subject): 8 Steps to better Transaction Log throughput.

You might be experiencing VLF fragmentation: Transaction Log VLFs - too many or too few?. Run this command to find out:

DBCC LOGINFO;

Have you followed this standard procedure to shrink the Log:

1) Backup your transaction log (even if you are in simple mode) to clear all activity.

BACKUP LOG [MyDB] 
TO DISK = N'E:\db.bak' 
GO 

2) Shrink the transaction log.

USE [MyDB]
GO

DBCC SHRINKFILE ('MyDB_Log', TRUNCATEONLY)
GO

3) Modify the size of the transaction log and configure your autogrowth:

USE [MyDB]
GO

ALTER DATABASE [MyDB] 
    MODIFY FILE ( NAME = N'MyDB_Log', SIZE = 1024000KB, FILEGROWTH = 1024000KB)
GO