Reindex/Reorg Space Concerns

Question

Post reply

Reindex/Reorg Space Concerns

rew-370421

Hall of Fame

Points: 3640
More actions
June 24, 2009 at 10:21 am

#135539

I'm using SQL Standard Edition (9.0.4035)
I have a database with a "Recovery Model" set to FULL. When I reindex/reorg Index, the MDF & LDF files grow alot: which is expected. Setting the MODEL to Bulk or SIMPLE has NO effect on lessening the file growths.
How do I reclaim the space taken up by the Reinex/Reorg process? This eats up a lot of disk space.
Should I perform a SHRINK on the database and its files? What's the best method to get this space back?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

karthik_sql Old Hand Points: 342 More actions · Answer 1

Please try the below

1. Put db to Simple recovery

2. increase the max size

3. run maint commands

4. change to FULL recovery

5. take a log dump

6. it its not ok, do shrink the Db files.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

Data files for databases tend to grow - why are you worried about recovering the disk space? The next time you reindex/reorg the data file is just going to grow. By shrinking and growing you are going to cause file level fragmentation which is going to affect performance.

I would let the data file grow and then increase the max size to account for future database growth and then monitor it to make sure you always have space available to reindex and grow.

As for the log, again - if it needs that much space to perform the operation then it is just going to grow the next time you perform that job. I would not shrink the file - I would actually grow the file to be a bit larger and leave it alone.

Review the article I link to in my signature for more information on how to manage the transaction logs.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

George M Parker SSCertifiable Points: 5412 More actions · Answer 3

See Paul Randall's blog about why you should never shrink SQL data files.

http://www.sqlskills.com/blogs/paul/Default.aspx#p0

rew-370421 Hall of Fame Points: 3640 More actions · Answer 4

Thanks for all the information.

Also, I want to shrink (or RECLAIM) the space that the REORG/REBUILD of an Index consumes. Disk space is always an issue.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

karthik_sql (6/24/2009)
Please try the below
1. Put db to Simple recovery

Absolutely NOT! That will make a mess of the log file for purposes of backup.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

You can specify that the sort done during the rebuild will be done in TempDB which should make your life a whole lot easier when it comes to space used.

Of course, the DB does have to be SQL Server 2005 or greater for it to actually use TempDB for the sort. Specifying that the sort takes place in TempDB has no effect on 2k databases even if you are doing it from SSMS or a 2k5 scheduled job.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)