Post reply

Repeatable statements that cause need for DBCC checktable repair_rebuild

  • June 14, 2013 at 6:09 am

    #273834

    I have a process that imports some data and massages it by running standard CRUD statements. I then call the following to make sure that my DB is happy. I do this because the process migrates a ton of data.

    declare @t varchar(250)

    select table_name INTO #tables from information_schema.tables where TABLE_TYPE = 'Base Table'

    while EXISTS(select table_name from #tables)

    BEGIN

    select top 1 @t = table_name from #tables

    print('Reindexing ' + @t)

    DBCC DBReindex (@t, '', 80)

    delete #tables where TABLE_NAME = @t

    END

    -- Update all statistics

    print('Updating statistics')

    EXEC sp_updatestats

    drop table #tables

    I then massage the data some more and run the reindex statement again. I have a certain data set that after running the statement the second time show the following:

    DBCC results for 'sys.sysidxstats'.

    Msg 2511, Level 16, State 1, Line 1

    Table error: Object ID 54, index ID 2, partition ID 562949956960256, alloc unit ID 562949956960256

    (type In-row data). Keys out of order on page (1:3757), slots 10 and 11.

    There are 901 rows in 33 pages for object "sys.sysidxstats".

    This is repeatable. It clears up after I run DBCC checktable ('sys.sysidxstats', REPAIR_REBUILD). Oddly, with a different (even larger) set of data and the same steps, the issue does not appear. That makes me think it's something in the source data that's causing it.

    My question: Am I causing this by calling reindex twice? Is something in my data causing this? Could my indexes be bad and be getting screwed up by my CRUD statements?

    One concern is that I plan on performing the reindexing statement in production once a week. Should I?

    Any help would be greatly appreciated.

    Thanks

    ST

  • June 14, 2013 at 6:16 am

    #1624064

    Which version of SQL Server? There's a documented bug with DBCC DBREINDEX in 2005:

    http://www.sqlskills.com/blogs/paul/corruption-bug-that-people-are-hitting-msg-2511-using-dbcc-dbreindex/

    Note that the command is deprecated anyway and you should use the ALTER INDEX syntax instead.

  • June 14, 2013 at 7:05 am

    #1624092

    I'm using SQL2008R2

    I didn't know the command was deprecated.

  • June 15, 2013 at 7:29 am

    #1624343

    Any ideas? I thought this type of issue was hardware or power related, not data.

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

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