Post reply

Rebuild index job is causing index corruption?

  • May 29, 2016 at 5:30 am

    #327326

    Hi

    I posted previously "Corrupted index - find out what caused it".

    I've had the file system checked, check disk etc no issues. Rebooted server

    It is running on SQL 2005. Workgroup edition.

    It effects a single indexed view in one DB. The view has a clustered index and a unique non clustered index.

    It would SEEM the maintenance job to rebuild indices is causing corruption. This job

    1. re builds all indices in selected dbs

    2. runs checkdb on these DBs

    I put step 2 in to confirm the corruption was caused by index rebuild.

    A valid checkdb ran 30 mins before reindex. There was no activity on server in this 30mins.

    I will now put an extra checkdb before the reindex so I know exactly when it happens so I check before and after rebuild.

    The problem has come about since I introduced an indexed view. As it's workgroup edition I have to use (NOEXPAND) in queries to get the queries to use the view indices.

    Any ideas?

    Thanks for reading my post

  • May 29, 2016 at 5:39 pm

    #1881956

    Seems like you uncovered a bug. Unfortunately, they not going to fix it because it's in a version of SQL Server that has been out of support for quite some time.

    --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)

  • May 30, 2016 at 8:34 am

    #1882008

    Thanks for taking the time Jeff. A server and SQL upgrade are in the pipeline.

    I've seen posts about SQL2012 & 2014 having issues with rebuilding indices w.r.t corruption.

    First time I've used an index view, perfect for my situation.

    I'll continue to monitor it.

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

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