Technical Article

Shrink all databases

,

Procedure changes all databases' recovery mode to simple and shrinks them all (or at least it tries to).

/************************************************************************
*
* Author Rafal Skotak
* Purpose Procedure changes recovery mode ao all databases to SIMPLE mode,
* shrinks them
* Date 2008-01-14
*
************************************************************************/
if exists(select * from sysobjects where id = object_id('dbo.proc_shrink_databases') and xtype = 'P')
drop procedure dbo.proc_shrink_databases
go

create procedure dbo.proc_shrink_databases
as
begin
set nocount on    

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]) 
select 
name 
from 
master..sysdatabases 
where
dbid > 4 --- skip master, tempdb, model and msdb databases

declare @db_name sysname

set @db_name = ''

while @db_name is not null
begin
set @db_name = NULL

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

if @db_name is NULL
break

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
end

drop table #temp_dbs_table
end
go

exec dbo.proc_shrink_databases

Rate

1.93 (14)

Share

Share

Rate

1.93 (14)