Just run this code in query analyzer against the master db or set up a agent job to run it on a schedule. It will resize all the transaction logs on the server.
/*
Function: For each user database remove all the freespace and truncate the transaction log.
Instructions: Run against master, the script will use the system catalog to produce a list of databases.
If you want to excluce a database add the excluded db name to the 'not in' list below.
cjm@integer.org
http://www.integer.org
Copyright (C) 2003 Cameron Michelis copying and redistribution of this file is permitted
provided this notice and the above comments are preserved.
*/
Set quoted_identifier off
use master
go
DECLARE @dataname varchar(30)
DECLARE @dataname_header varchar(75)
DECLARE datanames_cursor CURSOR FOR SELECT name FROM sysdatabases
WHERE name not in ('master', 'pubs', 'tempdb', 'model', 'northwind', '2024', 'MOBILESQL', 'THEGLNDB')
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM datanames_cursor INTO @dataname
CONTINUE
END
SELECT @dataname_header = "Database " + RTRIM(UPPER(@dataname))
PRINT " "
PRINT @dataname_header
EXEC("BACKUP LOG " + @dataname + " WITH TRUNCATE_ONLY")
EXEC("DBCC SHRINKDATABASE (" + @dataname + ",TRUNCATEONLY)")
FETCH NEXT FROM datanames_cursor INTO @dataname
END
DEALLOCATE datanames_cursor
PRINT ""
PRINT " "
PRINT "Free space removed and transaction log truncated for each user database"