Shrinking SQL Server Log Files

Inside Microsoft SQL Server 2005: The Storage Engine

Review

Relevance: Chapter 5 has great, in-depth, information on logging and recovery, and covers log-file growth, the check-point process, and information about virtual log files. See pages 92-93 for information about database and log files. Page 99 has some great information on DBCC SHRINKFILE.

Inside Microsoft SQL Server 2000

Review

Relevance: Chapter 5 (starting around page 175) provides some great information on database file sizes and growth - as well as information on shrinking files. Pages 182+ cover information about logging mechanics and truncation as well as the role of virtual log files.

Microsoft SQL Server 2005 Administrator's Pocket Consultant

Review

Relevance: Page 165 provides very rudimentary information on database file types - including log files. The rest of the same chapter covers database management concepts (including information on managing and sizing log files (pages 189 - 194)).

Microsoft Help and Support [link]

Relevance: If you need additional help troubleshooting excessive log file growth, make sure to check out both of these great KB articles:
  317375
  873235

(Both articles apply to all versions of SQL Server.)

SQL Server Books Online [link]

Relevance: Check out documentation for the following T-SQL and objects used in this video:

SQL Server 2000
  CHECKPOINT
  DBCC OPENTRAN
  DBCC INPUTBUFFER
  sysprocesses pseudo-table
  DBCC SHRINKFILE

SQL Server 2005 /2008
  CHECKPOINT
  DBCC OPENTRAN
  DBCC INPUTBUFFER
  sysprocesses pseudo-table
  sysdatabases DMV
  DBCC SHRINKFILE

SQLSkills.com [link]

Relevance: Paul S. Randal has a great blog post outlining causes for heavy log file usage: What can cause log reads and other transaction log questions.

Kalen Delany's Blog [link]

Relevance: In a great blog post, Kalen points out that sysprocesses does a better job of tracking-down orphaned transactions than other DMVs in SQL Server 2005.