Post reply

Is Maintenance Plan Reindex Appropriate for Large Table?

  • June 29, 2007 at 8:34 am

    #69350

    I have a 280gb database which has about 100gb available space; autogrowth Off.  The maintenance plan reindex has failed with the following error:

    Could not allocate space for object '(SYSTEM table id: -356785194)' in database 'ProdDB' because the 'PRIMARY' filegroup is full.

    The failure I believe is due to the reindexing of a 120gb daily transaction table for which there is not enough free space to complete.  My options are

    1) To grow the database (disk space is not an issue) or

    2) Discontinue the maintenance plan reindexing and write an indexing script that does not attempt a DBREINDEX on the daily transaction table .

    Option 1 is an easy fix, however, I am not sure that a reorg of the clustered index on the transaction table is necessary each week since the clustered index is on Process_date, it is loaded daily, hence the data is already ordered and the process_date will never change.

    Option 2 will require some work to script out the DBREINDEX for each table in the DB and do domething different for the large transaction table to avoid the reorg of the table each week and the increased processing load it entails.

    I looking for opinions or insights on what might be the better approach.

    Thanks

     

  • June 29, 2007 at 10:00 am

    #716245

    For really large tables, you might want to use DBCC INDEXDEFRAG, instead of reindexing.  It's an online operation that can be run while the database is active, and does the work in smaller steps.  If your database is in full recovery mode, make sure you do transaction log backups at very short intervals, say 5 to 15 minutes, because it generates large transaction logs.

     

     

     

  • June 29, 2007 at 10:01 am

    #716246

    Re-indexing logs a lot. If the log needs more space and it cannot grow automatically, that is probably why you're getting the error.

    I guess your best approach would be a combination of both option 1 and 2. I would do option 1 at least at the beginning, to let the log grow and see how much space it needs.

    Option 2, as you said, will require more time, but it will pay off. You could verify if re-indexing is necessary by checking the fragmentation through DBCC SHOWCONTIG and going from there.

    See example E in BOL for an idea of how to do this. You can modify the example to suit your particular need:

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/1df2123a-1197-4fff-91a3-25e3d8848aaa.htm

    -mr

Viewing 3 posts - 1 through 2 (of 2 total)

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