Never been to CodeSnippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world (or not, you can keep them private!)

MS SQL shrink log files for all databases (See related posts)


Truncates and shrinks log files for each user database on MS Sql server.

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
to sql mssql by hunter on Mon Sep 04 09:51:56 +0000 2006

Comments on this post

bobchauvin posts on Jul 09, 2009 at 16:44
Tweak to add a use statement prior to each db's call to SHRINKFILE, otherwise, it seems you would be shrinking the same files regardless of the db, as the fileid for all log files will be 2 and possible others for all dbs.

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 @sLogFileName varchar(55)
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))+'', 500) ''
fetch next from LogFiles into @iLogFile
end
set @tsql = ''USE [?]; '' + @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql
--print @tsql --for debugging
exec(@tsql)
close LogFiles
DEALLOCATE LogFiles
end'

exec sp_msforeachdb @ssql
bobchauvin posts on Jul 09, 2009 at 16:55
Sorry, my bad. Will work as you described as there is a us at the top.

You need to create an account or log in to post comments to this site.


Related Posts