The transaction logs on your SQL-server can fill up a lot of disk space. In my case, I monthly needed to truncate all the log files from the databases that I get from our production environment. These log files are really huge. I need to truncate them because I do not need the logs on my development machine. I made this little T-SQL script to make my (and your) life easier.
NOTE! This works for pre ”SQL Server 2008” because BACKUP LOG with TRUNCATE_ONLY is discontinued. An updated script can be found here.
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') 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+'Backup Log '+@db+' with truncate_only;' SET @sql=@sql+'DBCC shrinkfile ('+@logname+', 2);' --print @sql EXEC sp_executesql @sql,N'@logname varchar(300)',@logname fetch cDB into @db end close cDB deallocate cDB
2 reaktioner till “Shrinking ALL Truncate Log Files”