Post reply

Reindex/Reorg Space Concerns

  • 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?

  • June 24, 2009 at 10:55 am

    #1014784

    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.

  • June 24, 2009 at 2:25 pm

    #1014932

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • June 24, 2009 at 3:29 pm

    #1014962

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

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

  • June 26, 2009 at 1:37 pm

    #1016260

    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.

  • June 26, 2009 at 2:46 pm

    #1016281

    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)
    Intro to Tally Tables and Functions

  • June 26, 2009 at 2:49 pm

    #1016282

    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)
    Intro to Tally Tables and Functions

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

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