Discussion:
SQL Server Rebuild/Reorganize Index Task failed for OperationsManagerDW
(too old to reply)
Steven Yeung
2008-07-15 01:31:31 UTC
Permalink
Hi,

I've scheduled a maintenance plan to rebuild/reorganize the index of
OperationsManagerDW. However, the fail to complete. In the log, the
following paragraph was found.

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.

Some experts in SQL Server Forum teach me to run a query check the
constraint name of Event.EventStage table.

And the result found is PK__EventStage__4954A5DA

So someone suggested to name the constraints explictly instead of
relying on generated names. To do this, to run a SQL like:

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

But I would like to know will this corrupt the functions in SCOM.

Can anyone help me to solve the problem?

Thank you so much!

Regards,
Steven
Kevin N. Andersen
2008-07-15 08:00:47 UTC
Permalink
Hi,

We're experiencing the same issue as Steven and are very interested in a
solution to this problem as well.
--
Best regards / Venlig hilsen

Kevin N. Andersen
Windows System Administrator
Post by Steven Yeung
Hi,
I've scheduled a maintenance plan to rebuild/reorganize the index of
OperationsManagerDW. However, the fail to complete. In the log, the
following paragraph was found.
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.
Some experts in SQL Server Forum teach me to run a query check the
constraint name of Event.EventStage table.
And the result found is PK__EventStage__4954A5DA
So someone suggested to name the constraints explictly instead of
ALTER TABLE [Event].[EventStage]
ADD CONSTRAINT PK_EventStage
PRIMARY KEY (EventID)
But I would like to know will this corrupt the functions in SCOM.
Can anyone help me to solve the problem?
Thank you so much!
Regards,
Steven
Kevin Holman
2008-07-15 12:49:16 UTC
Permalink
There is no need to build a reindex/reorganize job for the DW - doing so can
cause performance problems during the job, especially if you are trying to
run a vanilla SQL maintenance plan and reindex the entire DW at once.

The DW is fully self maintaining, we run scripts constantly to measure the
fragmentation on a table by table, index by index basis... and take
appropriate action.

I document the activities here:
http://blogs.technet.com/kevinholman/archive/2008/04/12/what-sql-maintenance-should-i-perform-on-my-opsmgr-databases.aspx


There are several tables in OpsMgr that are dynamically generated by design,
and breaking this will break the product and leave you in an unsupportable
state. Staging tables should not be indexed anyway - they are for temporary
data storage and wont benefit from a reindex since the same data will not
even exist there between jobs.
Post by Kevin N. Andersen
Hi,
We're experiencing the same issue as Steven and are very interested in a
solution to this problem as well.
--
Best regards / Venlig hilsen
Kevin N. Andersen
Windows System Administrator
Post by Steven Yeung
Hi,
I've scheduled a maintenance plan to rebuild/reorganize the index of
OperationsManagerDW. However, the fail to complete. In the log, the
following paragraph was found.
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.
Some experts in SQL Server Forum teach me to run a query check the
constraint name of Event.EventStage table.
And the result found is PK__EventStage__4954A5DA
So someone suggested to name the constraints explictly instead of
ALTER TABLE [Event].[EventStage]
ADD CONSTRAINT PK_EventStage
PRIMARY KEY (EventID)
But I would like to know will this corrupt the functions in SCOM.
Can anyone help me to solve the problem?
Thank you so much!
Regards,
Steven
Steven Yeung
2008-07-16 06:45:05 UTC
Permalink
Hi Kevin,

Thank you so much! Your information is extremely useful!

Regards,
Steven
Post by Kevin Holman
There is no need to build a reindex/reorganize job for the DW - doing so can
cause performance problems during the job, especially if you are trying to
run a vanilla SQL maintenance plan and reindex the entire DW at once.
The DW is fully self maintaining, we run scripts constantly to measure the
fragmentation on a table by table, index by index basis... and take
appropriate action.
I document the activities here:http://blogs.technet.com/kevinholman/archive/2008/04/12/what-sql-main...
There are several tables in OpsMgr that are dynamically generated by design,
and breaking this will break the product and leave you in an unsupportable
state. Staging tables should not be indexed anyway - they are for temporary
data storage and wont benefit from a reindex since the same data will not
even exist there between jobs.
Post by Kevin N. Andersen
Hi,
We're experiencing the same issue as Steven and are very interested in a
solution to this problem as well.
--
Best regards / Venlig hilsen
Kevin N. Andersen
Windows System Administrator
Post by Steven Yeung
Hi,
I've scheduled a maintenance plan to rebuild/reorganize the index of
OperationsManagerDW. However, the fail to complete. In the log, the
following paragraph was found.
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.
Some experts in SQL Server Forum teach me to run a query check the
constraint name of Event.EventStage table.
And the result found is PK__EventStage__4954A5DA
So someone suggested to name the constraints explictly instead of
ALTER TABLE [Event].[EventStage]
ADD CONSTRAINT PK_EventStage
PRIMARY KEY (EventID)
But I would like to know will this corrupt the functions in SCOM.
Can anyone help me to solve the problem?
Thank you so much!
Regards,
Steven- 隱藏被引用文字 -
- 顯示被引用文字 -
David Makololo
2010-09-10 06:03:02 UTC
Permalink
Hi,

i just registered today and im willing to learn a lot with you guys.

Im David and im 22years i dont know much about programming,SQL or databases, i studied Networking im an intern in a very good compony that allows me to express my self.

After 2 months of doing the Application support i now handle the daily checklist which i struggle lot my jobs fail daily.

errors as follows:
ReportServer.DBCC
OperationsManagerAC.ReIndex
OperationsManagerDW.ReIndex

regards

David
Post by Steven Yeung
Hi,
I've scheduled a maintenance plan to rebuild/reorganize the index of
OperationsManagerDW. However, the fail to complete. In the log, the
following paragraph was found.
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.
Some experts in SQL Server Forum teach me to run a query check the
constraint name of Event.EventStage table.
And the result found is PK__EventStage__4954A5DA
So someone suggested to name the constraints explictly instead of
ALTER TABLE [Event].[EventStage]
ADD CONSTRAINT PK_EventStage
PRIMARY KEY (EventID)
But I would like to know will this corrupt the functions in SCOM.
Can anyone help me to solve the problem?
Thank you so much!
Regards,
Steven
Post by Kevin N. Andersen
Hi,
We're experiencing the same issue as Steven and are very interested in a
solution to this problem as well.
--
Best regards / Venlig hilsen
Kevin N. Andersen
Windows System Administrator
Post by Kevin Holman
There is no need to build a reindex/reorganize job for the DW - doing so can
cause performance problems during the job, especially if you are trying to
run a vanilla SQL maintenance plan and reindex the entire DW at once.
The DW is fully self maintaining, we run scripts constantly to measure the
fragmentation on a table by table, index by index basis... and take
appropriate action.
http://blogs.technet.com/kevinholman/archive/2008/04/12/what-sql-maintenance-should-i-perform-on-my-opsmgr-databases.aspx
There are several tables in OpsMgr that are dynamically generated by design,
and breaking this will break the product and leave you in an unsupportable
state. Staging tables should not be indexed anyway - they are for temporary
data storage and wont benefit from a reindex since the same data will not
even exist there between jobs.
Post by Steven Yeung
Hi Kevin,
Thank you so much! Your information is extremely useful!
Regards,
Steven
On 7=A4=EB15=A4=E9, =A4U=A4=C88=AE=C949=A4=C0, "Kevin Holman" <kevin.hol...=
can
o
e
ve/2008/04/12/what-sql-main...
gn,
e
ary
a
=3D
Submitted via EggHeadCafe - Software Developer Portal of Choice
MongoDb vs SQL Server Basic Speed Tests
http://www.eggheadcafe.com/tutorials/aspnet/6f573869-c8eb-40c3-9946-2f61e0163966/mongodb-vs-sql-server-basic-speed-tests.aspx
Loading...