Aug 4

Written by: Steve Gray
8/4/2010 8:46 AM 

This code was written for SQL 2000, but should run on later versions with little modification. I think that ‘sysdatabases’ might need to be changes to ‘sys.databases’

This will loop through all the dbs on a server with a database id > 4, which on SQL 2000 will eliminate the system dbs. It will truncate the logs for all the databases

DECLARE @db varchar(255)
declare @sql varchar(200)
DECLARE curName CURSOR LOCAL FAST_FORWARD FOR 
    select name
        from sysdatabases
        where dbid > 4
        order by dbid
        
OPEN curName
WHILE 1=1 
BEGIN
    FETCH NEXT FROM curName INTO @db
    if @@fetch_status <> 0 begin
        break
    end 
    set @sql = 'backup log ' + @db + ' with truncate_only'
    print @sql
    exec (@sql)
    set @sql = 'DBCC SHRINKDATABASE ( ' + @db + ' ,10)'
    print @sql
    exec (@sql)
END
CLOSE curName
DEALLOCATE curName