Rebuild Index keeps failing SQL2005

  • Failed: (-1073548784) Executing the query "ALTER INDEX [PK__someName] ON [Event].[Name] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )" failed with the following error: "Cannot find index". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  • write a query to rebuild the index on it's own - drop it into a query window and either show estimated plan or parse to check for errors. I'd check ownership/schema etc. was correct too.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • if someone has dropped the index/table during your operation, it is obvious your statement fails.

    set nocount on

    declare @OnlineRebuild char(1)

    Set @OnlineRebuild = 'Y'

    print '-- Begin ' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;

    Declare @SQLStmt varchar(max);

    DECLARE @Tellerke BIGINT

    SET @Tellerke = 0

    declare c1 cursor for

    --print 'use [' + db_name() + ']' + char(10) + 'GO'

    -- Using a CTE to avoid multiple scans

    -- Select Object that cannot use Online-reindexing

    -- (disabled indexes, colomns of datatype (n)text, image, (n)varchar(max),.. )

    WITH cteExcludedObjects (TABLE_SCHEMA , TABLE_NAME, INDEX_NAME, INDEX_ID )

    AS (

    Select T.TABLE_SCHEMA , T.TABLE_NAME, I.[name], I.[index_id]

    from INFORMATION_SCHEMA.Tables T with (nolock)

    Inner Join sys.indexes I with (nolock)

    ON I.[object_id] = object_id('[' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + ']')

    and I.is_disabled = 0 -- Only select active indexes !

    where exists ( Select *

    from sys.indexes I with (nolock)

    where I.[object_id] = object_id('[' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + ']')

    and I.is_disabled = 1 )

    OR EXISTS (SELECT *

    FROM INFORMATION_SCHEMA.Columns C

    Where C.TABLE_SCHEMA = T.TABLE_SCHEMA

    AND C.TABLE_NAME = T.TABLE_NAME

    -- exclude text, ntext, image, varchar(max), nvarchar(max), varbinary(max)

    AND( C.CHARACTER_OCTET_LENGTH > 8000

    OR C.CHARACTER_OCTET_LENGTH = (-1)

    )

    )

    )

    Select 'use [' + db_name() + '] ;

    print '' tabel [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] '' + convert(varchar(25),getdate(),121) ;

    ALTER INDEX ALL ON [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] REBUILD '

    + case @OnlineRebuild when 'Y' then ' WITH ( ONLINE = ON )' else '' end + ' ;'

    from INFORMATION_SCHEMA.Tables T with (nolock)

    Left Join cteExcludedObjects D

    on T.TABLE_SCHEMA = D.TABLE_SCHEMA

    and T.TABLE_NAME = D.TABLE_NAME

    Where T.TABLE_TYPE = 'BASE TABLE'

    and D.TABLE_SCHEMA IS NULL

    and T.TABLE_NAME <> 'dtproperties'

    Union ALL

    Select 'use [' + db_name() + '];

    print '' tabel [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] IX ' + cast(D.INDEX_ID as varchar(15)) + ' [' + D.INDEX_NAME + '] '' + convert(varchar(25),getdate(),121) ;

    ALTER INDEX [' + D.INDEX_NAME + '] ON [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] REBUILD WITH ( ONLINE = OFF ) ;'

    from INFORMATION_SCHEMA.Tables T with (nolock)

    inner join cteExcludedObjects D

    on T.TABLE_SCHEMA = D.TABLE_SCHEMA

    and T.TABLE_NAME = D.TABLE_NAME

    Where T.TABLE_TYPE = 'BASE TABLE'

    and INDEX_NAME is not NULL -- Heap niet van toepassing

    order by 1 ;

    open c1

    FETCH NEXT FROM c1 INTO @SQLStmt

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- print @sqlstmt

    exec ( @SQLStmt )

    set @Tellerke = @Tellerke + 1

    FETCH NEXT FROM c1 INTO @SQLStmt

    END

    -- Cursor afsluiten

    CLOSE c1

    DEALLOCATE c1

    Print ' '

    Print 'Number of Objects handled.'

    Print '--------------------------'

    Print @Tellerke

    print ' '

    print '-- Usage ' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;

    DBCC UPDATEUSAGE (0) with count_rows ;

    print '-- Usage Statistics ' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;

    EXEC sp_updatestats ;

    print '-- The end ' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

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

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