I have made a simple script for truncating sql-server log files. In ”SQL-Server 2008″ and later versions the ”BACKUP LOG with TRUNCATE_ONLY” command is discontinued and my old script does not work any longer. The updated script works by setting the databases to simple recovery mode.

declare @db varchar(300)
declare @logname as varchar(300)
declare @sql nvarchar(1000)

declare cDB cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.name NOT IN('master','model','pubs','msdb','tempdb')
ORDER BY name

open cDB
fetch cDB into @db

while (@@fetch_status = 0)
begin

--print @db
SET @sql='SELECT @logname=name FROM '+@db+'.dbo.Sysfiles WHERE fileid=2'
print @sql
EXEC sp_executesql @sql,N'@logname varchar(300) out,@db varchar(300)',@logname out,@db
print @logname

SET @sql='Use '+@db+';'
SET @sql=@sql+'ALTER DATABASE '+@db+' SET RECOVERY SIMPLE;'
SET @sql=@sql+'DBCC shrinkfile ('+@logname+', 1);'
print @sql
EXEC sp_executesql @sql,N'@logname varchar(300)',@logname

fetch cDB into @db
end
close cDB
deallocate cDB

online casino bonus

En kommentar till “”

Lämna ett svar

E-postadressen publiceras inte. Obligatoriska fält är märkta *

Denna webbplats använder Akismet för att minska skräppost. Lär dig hur din kommentardata bearbetas.