Shrink all databases in SQL instance

CCBelux
CCBelux used Ask the Experts™
on
Hi,

I'm lookinh gor a way to shringk all the databases in one of our SQL 2005 instance.
All our DBs are set in Full backup mode, so I need a script that set them to Simple, Shrink and then set them back to Full backup mode.

I've found a script on the Net and I tried to modify it, but I get an error.
The goal is to have a query that will process all users databases one by one.

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' 
  ALTER DATABASE @db SET RECOVERY SIMPLE
  DBCC shrinkdatabase (@db)
  ALTER DATABASE @db SET RECOVERY FULL
  fetch next from c into @db
end
close c
deallocate c

Open in new window


This query returns the error message

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '@db'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '@db'.

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
you can use this which is easier...

DBCC SHRINKDATABASE (<database name>, <percentage to shrink> )

or

DBCC SHRINKFILE ( file , EMPTYFILE ) [WITH NO_INFOMSGS]

DBCC SHRINKFILE ( file , target_size
              [, {NOTRUNCATE | TRUNCATEONLY }] )  [WITH NO_INFOMSGS ]
Chief Database Architect
Commented:
Enjoy

IF EXISTS(select * from sysobjects where id = object_id('dbo.SP_ShrinkAllDatabasesOnServer') and xtype = 'P')
	DROP PROCEDURE dbo.SP_ShrinkAllDatabasesOnServer
GO

CREATE PROCEDURE dbo.SP_ShrinkAllDatabasesOnServer
AS
BEGIN
	CREATE TABLE #TempDatabasesTable
	(
		[DatabaseName] sysname not null primary key,
		Mod tinyint not null default 1
	)
	INSERT INTO #TempDatabasesTable ([DatabaseName]) 
	SELECT 
		name 
	FROM 
		master..sysdatabases 
	WHERE
		dbid > 4 
	DECLARE @DatabaseName sysname

	SET @DatabaseName = ''

	WHILE @DatabaseName is not null
	BEGIN
		SET @DatabaseName = NULL

		SELECT TOP 1 @DatabaseName = [DatabaseName] from #TempDatabasesTable where Mod = 1

		IF @DatabaseName is NULL
			break

		print '*******************************************************************'
		print '> DB: ' + @DatabaseName
		print '> SET RECOVERY MODE SIMPLE'
		declare @SqlCommand nvarchar(4000)
		set @SqlCommand = 'ALTER DATABASE [' + @DatabaseName + '] SET recovery simple'
		exec sp_executesql @SqlCommand
		print '> Shrinking database'
		set @SqlCommand = 'dbcc shrinkdatabase([' + @DatabaseName + '])'
		exec sp_executesql @SqlCommand
		update #TempDatabasesTable set Mod = 0 where [DatabaseName] = @DatabaseName
	end
	DROP TABLE #TempDatabasesTable
END
GO
exec dbo.SP_ShrinkAllDatabasesOnServer

Open in new window

Top Expert 2012

Commented:
All our DBs are set in Full backup mode, so I need a script that set them to Simple, Shrink and then set them back to Full backup mode.
I trust you realize that this is considered a very bad idea and defeats the whole point of having it in Full Recovery Model in the first place.  You will break the Transaction log chain and will not be able to do a point-in-time restore.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Aaron ShiloChief Database Architect

Commented:
then remove the switch ti simple mode the script will still work,

Author

Commented:
Hi ashilo and thanks for the detailed answer.
When I try to run your script, i get the message
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.SP_ShrinkAllDatabasesOnServerte'.

Open in new window


Regarding the Simple-Full mode and T-Log, yes I do understand it defeats the purpose. I only want to have that script because I'm preparing SQL for a SharePoint migration and according to a Technet article, all logs should be shrunk beforehand. So I'm going to take a full backup of all DBs, then shrink them all, then start the migration.

Author

Commented:
@wshark83 thanks for your reply, but this won't help me sith the simple/full backup mode or going through each DB in the instance
Aaron ShiloChief Database Architect

Commented:
regarding the error message, notice the error is looking for a procedure called "SP_ShrinkAllDatabasesOnServerte"
you have a probably by mistake added an "E" to the end of the name of the procedure
just make sure its "SP_ShrinkAllDatabasesOnServer"

Author

Commented:
worked like a charm. this is an excellent script!!
thanks a lot
Aaron ShiloChief Database Architect

Commented:
im glad i could help :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial