mssql truncate all log files

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 

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>