Terug naar hoofdinhoud

The code below, get a list of non system databases, set the database to readonly and then shrink the file.

I have kept this code in a few SQL Server boxes using SQL Agent Job, where space is always an issue.

On Sat/Sun night every week, it start running and shrink all the databases within few hours (depending upon the size of databases).

declare @db varchar(255)
declare c cursor for
select name from sys.databases where is_read_only=0 and state=0
  and name not in ('master','model','tempdb','msdb')
open c
fetch c into @db
while @@fetch_status=0
begin
  exec SP_dboption @db,'trunc. log on chkpt.','true'
  DBCC shrinkdatabase (@db)
  fetch next from c into @db
end
close c
deallocate c