Shrinking ALL Truncate Log Files
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.
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