Post reply

DBCC DBREINDEX on push transactional replication subscriber

  • June 17, 2008 at 9:05 am

    #72329

    Hello,

    I have a few questions so here I go!!

    After running DBCC SHOWCONTIG, I have determined that a few tables in a database could do with reindexing. They all have clustered indexes.

    I wanted to know what thresholds should be used to decide whether to use DBCC DBREINDEX or DBCC INDEXDEFRAG?

    The database in question is a push subscription database.

    I understand DBCC DBREINDEX exclusively locks a table, how will this affect replication?

    I appreciate data wont be inserted into the locked table, but will replication basically queue until the table is unlocked? Will the transactions be held on the publisher/distributer until the subscriber can accept requests?

    Also, I want to shrink the database after rebuilding the index, I am told that I should really shrink the database and then rebuild indexes is that correct? Is DBCC shrinkdatabase an online function?

    Just in case, this is a sql 2000 box.

    thanks in advance

  • June 17, 2008 at 9:33 am

    #829888

    DBReindex command is logged so log reader agent won't work as efficiently as it has more log to chew through.

    Regarding same question in other forum:

    http://www.sqlservercentral.com/Forums/Topic335623-5-1.aspx

    Normally you should avoid shrinking the database

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

    Go thru the below link for understanding IndexDefrag and DBREINDEX behaviour:

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

    Ur sequence is correct first shrink the database if it has too much free space in it and then reindex the database.

    Shrinkdatabase is an online operation but users will feel slowness while accessing the database--Experts please comment.

    Manu

  • July 1, 2008 at 9:28 am

    #836777

    Thanks.

    I apprieciate the thoughts behind not shrinking a database, is there another way giving the O/S back free space the database may have been holding onto?

    If I run shrinkdatabase, does this hold any locks on the database or objects within it?

    thanks

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

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