Shrink all databases and Set it to Simple in SQL Server

Normally, I need to run this script against dev sql server in order for me to free up some space

create table #temp_dbs_table
[db_name] sysname not null primary key,
[mod] tinyint not null default 1

insert into #temp_dbs_table ([db_name])
dbid > 4 --- skip master, tempdb, model and msdb databases

declare @db_name sysname

set @db_name = ''

while @db_name is not null
set @db_name = NULL

select top 1 @db_name = [db_name] from #temp_dbs_table where [mod] = 1

if @db_name is NULL

print '--------------------------------------------------'

print '> Database: ' + @db_name

print '> Changing recovery mode to simple'

declare @n_cmd nvarchar(4000)

set @n_cmd = 'alter database [' + @db_name + '] set recovery simple'

exec sp_executesql @n_cmd

print '> Shrinking database'

set @n_cmd = 'dbcc shrinkdatabase([' + @db_name + '])'

exec sp_executesql @n_cmd

update #temp_dbs_table set [mod] = 0 where [db_name] = @db_name

drop table #temp_dbs_table

I got this script from here

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s