How often do you backup your system? If you dont you can run the code below to clean-up the LOG, HOWEVER please be aware that IF this is a production system then you may need to research backing up the database and log which will preserve your data. (If this is still within your companies policy)
BACKUP LOG TestDB WITH NO_LOG
DBCC SHRINKFILE (TestDB_Log, 1000)
The 1st command issues a backup "DUMP" of completed transactions from your T-LOG and the 2nd will shrink the physical file down to 1GB or whatever size you tell it (IF you want).
It sounds like you don't backup your transaction log. Have you ever done a full backup? If not, DO IT NOW!!!
If you have done a full backup, backup the transaction log. This shrinks the log file (not immediately though). If you want to shrink it right away, you can BACKUP LOG dbname WITH TRUNCATE_ONLY. (That can be run even if you just backed up the log). You should immediatly do a full backup after truncating the log. Or you can shrink it using the DBCC SHRINKFILE command.
Jonathon - I'm interested in your comment reorganising data and index pages. I'm seeing a similar problem with out logs and data - with the logs twice the size as the data. We're changing the backup logs to an intraday process so that should help. However, let me give you my example:-
I update one table with 500,000 rows daily. This table has 8+ indexes on it (about 100 fields). Would the number of indexes contribute to the volume of the transaction log we're seeing? We're reviewing the indexes but this is an incremental process as the product is large.
Any help would be great.
Thanks Jonathan; just another quick one on this - so is an REINDEX more log intensive than a DEFRAG or do they have the same footprint regards to the transaction log.