Shrink Database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Shrink Database

Author

Topic

gcsin
Starting Member

2 Posts

Posted - 2008-10-01 : 02:40:25

I want to shrink all database at a time using job. For this I have to write a sp. Please help me, how i can write a sp to shrink all database one by one.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-01 : 14:38:00

CREATE procedure SP_SHRINK_ALL_DB
as
begin

declare @dbname varchar(50)

DECLARE dblist_cursor CURSOR for
select name from master.dbo.sysdatabases

OPEN dblist_cursor

FETCH NEXT FROM dblist_cursor
INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
DBCC SHRINKDATABASE(@dbname)
FETCh NEXT from dblist_cursor
into @dbname

END

CLOSE dblist_cursor
DEALLOCATE dblist_cursor
end

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-01 : 15:34:31

Read this for why you shouldn't shrink:

http://www.karaszi.com/SQLServer/info_dont_shrink.asp

gcsin
Starting Member

2 Posts

Posted - 2008-10-06 : 03:44:24

Thanks guys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources