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 &amp; 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

Related Posts with Thumbnails
Share and Enjoy:
  • Print
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • Blogplay


Trackbacks / Pingbacks

  1. Shrink All Log Files Within A MS SQL 2008 Instance | SharePoint Geek

Leave a Reply