Friday, January 31, 2014

Shrink LDF Files

The following script is what we used to shrink all of our sql server database log files:

-- database name
declare @name varchar(100)
-- path for backup files
declare @sql varchar(max)
set @sql = ''

declare db_cursor cursor for  

select [name]
from sys.databases
where database_id > 4 and state = 0
order by name 

open db_cursor
fetch next from db_cursor into @name
while @@fetch_status = 0
begin
       set @sql = 'use' +''+ '[' + @name + ']' + ';' + '' + 'dbcc shrinkfile(2,1)'
     --print @sql
       exec (@sql)
       fetch next from db_cursor into @name
end
close db_cursor
deallocate db_cursor

No comments:

Post a Comment