Shrink All Log Files Within A MS SQL Instance
This little SQL script is used to to shrink all the log files in a Microsoft SQL server instance. It uses the following function to get the job done: DBCC SHRINKFILE.
declare @ssql nvarchar(4000)<br />
set @ssql= '<br />
if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin<br />
use [?]<br />
declare @tsql nvarchar(4000) set @tsql = ''''<br />
declare @iLogFile int<br />
declare LogFiles cursor for<br />
select fileid from sysfiles where status & 0x40 = 0x40<br />
open LogFiles<br />
fetch next from LogFiles into @iLogFile<br />
while @@fetch_status = 0<br />
begin<br />
set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''<br />
fetch next from LogFiles into @iLogFile<br />
end<br />
set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql<br />
--print @tsql<br />
exec(@tsql)<br />
close LogFiles<br />
DEALLOCATE LogFiles<br />
end'<br />
exec sp_msforeachdb @ssql<br />
via CodeSnippets
April 7, 2010
|
Posted by Stewart
Categories:
Trackbacks / Pingbacks