Fail to rebuild/reorganize index

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steven Yeung - 14 Jul 2008 08:18 GMT

Hi,

I am very new to MSSQL Server. Recently, I've found some problems with
my Rebuild/Reorganize Index Task.

I can find the following in the error log. Can anyone tell me how may
I solve the problem?

Failed:(-1073548784) Executing the query "ALTER INDEX
[PK__EventStage__2B196C26] ON [Event].[EventStage] 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
'PK__EventStage__2B196C26'.". Possible failure reasons: Problems with
the query, "ResultSet" property not set correctly, parameters not set
correctly, or connection not established correctly.

Thank you very much!

Regards,
Steven

Reply to this Message

Uri Dimant - 14 Jul 2008 08:34 GMT

Steven
If you look at EventStage table , do you see PK
([PK__EventStage__2B196C26)?

select t.name as TABLE_NAME

, k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE

, c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION

from sys.key_constraints as k

join sys.tables as t

on t.object_id = k.parent_object_id

join sys.schemas as s

on s.schema_id = t.schema_id

join sys.index_columns as ic

on ic.object_id = t.object_id

and ic.index_id = k.unique_index_id

join sys.columns as c

on c.object_id = t.object_id

and c.column_id = ic.column_id

order by TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME, ORDINAL_POSITION;

PS.
Personally I try to avoid maintenance plans as much as I can.

> Hi,
>
[quoted text clipped - 17 lines]
> Regards,
> Steven

Reply to this Message

Steven Yeung - 14 Jul 2008 09:02 GMT

Hi,

Thank you so much for the reply!

Well, what I found is a bit difference. The one I found is
PK__EventStage__4CCF65AC.

Regards,
Steven

> Steven
> If you look at EventStage table , do you see PK
[quoted text clipped - 56 lines]
>
> - 顯示被引用文字 -

Reply to this Message

Dan Guzman - 14 Jul 2008 13:19 GMT

> Well, what I found is a bit difference. The one I found is
> PK__EventStage__4CCF65AC.

I suggest you name constraints explicitly instead of relying on generated
names. For example:

ALTER TABLE [Event].[EventStage]
ADD CONSTRAINT PK_EventStage
PRIMARY KEY (EventID)

> > " failed with the following error: "Cannot find index
> > 'PK__EventStage__2B196C26'.". Possible failure reasons: Problems with
> > the query, "ResultSet" property not set correctly, parameters not set
> > correctly, or connection not established correctly.

Perhaps the primary key constraint was recreated while the maintenenace task
was running so a new constraint name was generated.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Hi,

Thank you so much for the reply!

Well, what I found is a bit difference. The one I found is
PK__EventStage__4CCF65AC.

Regards,
Steven

On 7月14日, 下午3時34分, "Uri Dimant" <u...@iscar.co.il> wrote:

> Steven
> If you look at EventStage table , do you see PK
[quoted text clipped - 60 lines]
>
> - 顯示被引用文字 -

Reply to this Message

Steven Yeung - 15 Jul 2008 02:25 GMT

Hi,

Thank you very much. However, the database is created by Microsoft
SCOM, can I just do the modification?

Regards,
Steven

On 7月14日, 下午8時19分, "Dan Guzman" <guzma...@nospam-online.sbcglobal.net>
wrote:

> > Well, what I found is a bit difference. The one I found is
> > PK__EventStage__4CCF65AC.
[quoted text clipped - 98 lines]
>
> - 顯示被引用文字 -

Reply to this Message

Dan Guzman - 15 Jul 2008 03:29 GMT

> Thank you very much. However, the database is created by Microsoft
> SCOM, can I just do the modification?

With third party applications, I suggest you just leave the object names
alone. I suspect a concurrent DDL operation is what broke the maintenance
task.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> > Well, what I found is a bit difference. The one I found is
> > PK__EventStage__4CCF65AC.
[quoted text clipped - 102 lines]
>
> - 顯示被引用文字 -

Reply to this Message

Ola Hallengren - 24 Jul 2008 13:55 GMT

I have a SCOM solution. I can confirm that there is a lot of DDL changes
going on.

I have no real insight in how this works, but here are some statistics from
one week's DDL logging.

Object    EventType    Count
OperationsManagerDW.Event.EventStage    CREATE_TABLE    50139
OperationsManagerDW.Event.EventStage    GRANT_DATABASE    50139
OperationsManagerDW.Event.EventStage2Process    DROP_TABLE    50139
OperationsManagerDW.Event.EventStage2Process    GRANT_DATABASE    50139
OperationsManagerDW.Perf.PerformanceStage    CREATE_TABLE    21727
OperationsManagerDW.Perf.PerformanceStage    GRANT_DATABASE    21727
OperationsManagerDW.Perf.PerformanceStage2Process    DROP_TABLE    21727
OperationsManagerDW.Perf.PerformanceStage2Process    GRANT_DATABASE    21727

I guess that there are also some object renaming that is happening (since
it's not the same object name being created and being dropped).

I think that this is the reason for your failed index operations.

I'm using a stored procedure for index rebuild and reorganize. It is doing a
final check that the index exists just before doing the rebuild or
reorganize. It is working good with SCOM. Please use that if you like.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html

Ola Hallengren
http://ola.hallengren.com

> > Thank you very much. However, the database is created by Microsoft
> > SCOM, can I just do the modification?
[quoted text clipped - 119 lines]
> >
> > - ܳQޥΤr -

Reply to this Message