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
En kommentar till “”