mssql truncate all log files

Steve Stonebraker
December 8, 2010

Learn in MSSQL how to truncate all log files! This transact-sql statement uses the core truncation method recommended by Microsoft.mssql truncate all log files

mssql truncate all log files on a MSSQL 2000, 2005 server

DECLARE @db varchar(255)
declare @sql varchar(200)
DECLARE curName CURSOR LOCAL FAST_FORWARD FOR 
    select name
        from master.dbo.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 = 'DBCC SHRINKDATABASE ( ''' + @db + ''' ,2);'
    print @sql
    exec (@sql)
    set @sql = 'backup log ''' + @db + ''' with truncate_only;'
    print @sql
    exec (@sql)
    set @sql = 'DBCC SHRINKDATABASE ( ''' + @db + ''' ,2);'
    print @sql
    exec (@sql)
END
CLOSE curName
DEALLOCATE curName

Truncating a single Microsoft SQL Log Files for MSSQL 2000, 2005

USE DatabaseName
GO
DBCC SHRINKFILE(, 1)
BACKUP LOG  WITH TRUNCATE_ONLY
DBCC SHRINKFILE(, 1)
GO
Tags: , , , , , , , ,