View Single Post
Old 06-06-2004, 06:14 PM   #5 (permalink)
bobd123
Upright
 
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"
bobd123 is offline  
 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73