Monitoring and dumping SQL Server transaction logs

Every site should have a job that monitors and automatically dumps transaction logs for each database before log is filled up.
The following Microsoft Knowelege Base document lists some reasons why the transaction log fills up and never gets truncated, even though the "trunc. log on chkpnt" option is set to true and regular checkpoints are done during processing.

ARTICLE ID: 62866 19-FEB-2007 [sqlserver]
TITLE: Reasons Why SQL Transaction Log Is Not Being Truncated
PRODUCT: Microsoft SQL server
PROD/VER: winnt:4.2x,6.0,6.5,7.0,2000 Standard,2005 Standard/Developer/Enterprise/Express/Workgroup
http://support.microsoft.com/kb/62866

To avoid "Log Full" problems use the following SQL script that you can schedule to run every 30 minutes as a "Database Type" job having "All Day" schedule. Setup such job for every server that you want to monitor and automatically dump transaction log for each database before logs are filled up.

CREATE TABLE #log_stat (
    dbname char(30),
    logsize float,
    logspaceused float,
    status tinyint )

INSERT INTO #log_stat EXEC ('DBCC sqlperf(logspace)')

DECLARE @db_name char(30)

SELECT @db_name = min(dbname)
FROM #log_stat
WHERE logspaceused > 70

WHILE @db_name is not NULL
BEGIN
    /* Log is over 70 percent full. Dumping the log */
    EXEC ('DUMP TRAN ' + @db_name + ' with truncate_only')
   
    SELECT @db_name = min(dbname)
    FROM #log_stat
    WHERE logspaceused > 70
        and dbname > @db_name
END

DROP TABLE #log_stat