re-indexing 1

[swoodring]

I have a cursor set up to re-index our tables in our datawarehouse. I do this on the weekends. And when I come in on Monday I notice that the db has grown by about 10 gig. The 10 gig is in the free space. I'm wondering does it use this space temporarily when re-indexing and then free it up. And thus I need to perform a shrink db once the re-index cursor is complete.

Here's the cursor I'm using:
/* This script will re-index all the indexes in PBDSS. */

DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR for
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'Base Table'
DECLARE @Command varchar(255)

OPEN Tablecursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@Fetch_Status = 0
BEGIN
PRINT "Reindexing " + @TableName
DBCC DBREINDEX (@TableName)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor


And is there a way to have it not use so much space while its re-indexing. Right now I have the extra 10 gig but as we grow that would mean I need to keep and extra 10 gig around just for re-indexing. That seem a little excessive.
Stacy

 

[tlbroadbent]

Reindexing does use a lot of space. We've set up processes to shrink the databases and files after reindexing. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[ColinM]

Heres another easy way to do a reindex on all your tables:

exec sp_MSforeachtable @command1 = "print 'Reindexing ?'", @command2 = "dbcc dbreindex('?')"