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

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.









"Steven Yeung" <steveninchrist@gmail.com> wrote in message
news:5074ee35-9b63-45d2-807f-b79c305f40f1@w7g2000hsa.googlegroups.com...
> 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

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¤é, ¤U¤È3®É34¤À, "Uri Dimant" <u...@iscar.co.il> wrote:
> 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.
>
> "Steven Yeung" <steveninchr...@gmail.com> wrote in message
>
> news:5074ee35-9b63-45d2-807f-b79c305f40f1@w7g2000hsa.googlegroups.com...
>
>
>
> > 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- ÁôÂóQ¤Þ¥Î¤å¦r -
>
> - Åã¥Ü³Q¤Þ¥Î¤å¦r -

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

--
Hope this s.

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

"Steven Yeung" <steveninchrist@gmail.com> wrote in message
news:27df3557-7723-4789-86ee-54a14387374f@m45g2000hsb.googlegroups.com...
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¤é, ¤U¤È3®É34¤À, "Uri Dimant" <u...@iscar.co.il> wrote:
> 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.
>
> "Steven Yeung" <steveninchr...@gmail.com> wrote in message
>
> news:5074ee35-9b63-45d2-807f-b79c305f40f1@w7g2000hsa.googlegroups.com...
>
>
>
> > 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- ÁôÂóQ¤Þ¥Î¤å¦r -
>
> - Åã¥Ü³Q¤Þ¥Î¤å¦r -

Hi,

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

Regards,
Steven

On 7¤ë14¤é, ¤U¤È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.
>
> 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.
>
> --
> Hope this s.
>
> Dan Guzman
> SQL Server MVPhttp://weblogs.sqlteam.com/dang/
>
> "Steven Yeung" <steveninchr...@gmail.com> wrote in message
>
> news:27df3557-7723-4789-86ee-54a14387374f@m45g2000hsb.googlegroups.com...
> 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¤é, ¤U¤È3®É34¤À, "Uri Dimant" <u...@iscar.co.il> wrote:
>
>
>
> > 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.
>
> > "Steven Yeung" <steveninchr...@gmail.com> wrote in message
>
> >news:5074ee35-9b63-45d2-807f-b79c305f40f1@w7g2000hsa.googlegroups.com...
>
> > > 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- ÁôÂóQ¤Þ¥Î¤å¦r -
>
> > - Åã¥Ü³Q¤Þ¥Î¤å¦r -- ÁôÂóQ¤Þ¥Î¤å¦r -
>
> - Åã¥Ü³Q¤Þ¥Î¤å¦r -

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


--
Hope this s.

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

"Steven Yeung" <steveninchrist@gmail.com> wrote in message
news:96483653-400b-4912-8635-4e3178d7fa30@z66g2000hsc.googlegroups.com...
Hi,

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

Regards,
Steven

On 7¤ë14¤é, ¤U¤È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.
>
> 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.
>
> --
> Hope this s.
>
> Dan Guzman
> SQL Server MVPhttp://weblogs.sqlteam.com/dang/
>
> "Steven Yeung" <steveninchr...@gmail.com> wrote in message
>
> news:27df3557-7723-4789-86ee-54a14387374f@m45g2000hsb.googlegroups.com...
> 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¤é, ¤U¤È3®É34¤À, "Uri Dimant" <u...@iscar.co.il> wrote:
>
>
>
> > 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.
>
> > "Steven Yeung" <steveninchr...@gmail.com> wrote in message
>
> >news:5074ee35-9b63-45d2-807f-b79c305f40f1@w7g2000hsa.googlegroups.com...
>
> > > 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- ÁôÂóQ¤Þ¥Î¤å¦r -
>
> > - Åã¥Ü³Q¤Þ¥Î¤å¦r -- ÁôÂóQ¤Þ¥Î¤å¦r -
>
> - Åã¥Ü³Q¤Þ¥Î¤å¦r -

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/...1/3440068.html

Ola Hallengren
http://ola.hallengren.com



"Dan Guzman" wrote:

> > 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.
>
>
> --
> Hope this s.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "Steven Yeung" <steveninchrist@gmail.com> wrote in message
> news:96483653-400b-4912-8635-4e3178d7fa30@z66g2000hsc.googlegroups.com...
> Hi,
>
> Thank you very much. However, the database is created by Microsoft
> SCOM, can I just do the modification?
>
> Regards,
> Steven
>
> On 714, U819, "Dan Guzman" <guzma...@nospam-online.sbcglobal.net>
> wrote:
> > > 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.
> >
> > --
> > Hope this s.
> >
> > Dan Guzman
> > SQL Server MVPhttp://weblogs.sqlteam.com/dang/
> >
> > "Steven Yeung" <steveninchr...@gmail.com> wrote in message
> >
> > news:27df3557-7723-4789-86ee-54a14387374f@m45g2000hsb.googlegroups.com...
> > 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 714, U334, "Uri Dimant" <u...@iscar.co.il> wrote:
> >
> >
> >
> > > 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.
> >
> > > "Steven Yeung" <steveninchr...@gmail.com> wrote in message
> >
> > >news:5074ee35-9b63-45d2-807f-b79c305f40f1@w7g2000hsa.googlegroups.com...
> >
> > > > 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- óQޥΤr -
> >
> > > - ܳQޥΤr -- óQޥΤr -
> >
> > - ܳQޥΤr -
>