December 13, 2007 at 3:53 pm
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.
December 14, 2007 at 5:48 am
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/
December 14, 2007 at 5:59 am
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