Social Icons

twitter google plus linkedin rss feed

Pages

8.2.12

Shrink All the Databases

I wouldn’t recommend to do this in a production environment it doesn’t sounds to me too best practicy,  but if your development is running out of hard drive space I think this is a good alternative to shrinking the databases one by one…

The SQL script I use is this:

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
Shrink All The Databases

(I got it from here but mine is properly formatted :P)