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)