TSQL script to shrink all log files
I got alerted over the weekend to a SQL server running out of disk space on one of its drives. Upon further inspection, several of the LOG files had grown and were filling the log drive. I went to shrink the files, but realized there must be a better way.
A little googling and I found http://codesnippets.joyent.com/posts/show/665. This script cycles through all DBs and shrinks the LOG files for each DB. Simply schedule it with the SQL Server Agent and your good to go.
declare @ssql nvarchar(4000)
set @ssql= '
if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin
use [?]
declare @tsql nvarchar(4000) set @tsql = ''''
declare @iLogFile int
declare LogFiles cursor for
select fileid from sysfiles where status & 0x40 = 0x40
open LogFiles
fetch next from LogFiles into @iLogFile
while @@fetch_status = 0
begin
set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''
fetch next from LogFiles into @iLogFile
end
set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql
--print @tsql
exec(@tsql)
close LogFiles
DEALLOCATE LogFiles
end'
exec sp_msforeachdb @ssql
Do never run BACKUP LOG [?] WITH TRUNCATE_ONLY unless it is a test/play database. You lose the ability to restore the database to a point in time.
Here is a article why: http://sqlserverpedia.com/blog/sql-server-backup-and-restore/backup-log-with-truncate_only-like-a-bear-trap/
Be careful, as the sysfiles compatibility view is going to be removed from future versions of SQL:
http://msdn.microsoft.com/en-us/library/ms178009.aspx
…sorry should have said as well that TRUNCATE_ONLY is no longer available in SQL 2008 R2
Amazing post
SQL server works a lot in the software industry as best assignment service. People love to us ethics for the data shrinking purpose. Is server helps to increase the capacity of space in the system. This lead for the better performance of the system. All log files can be shrinking by such system.