Reorganize Index Task

Question

Post reply

Reorganize Index Task

lkarthikraj-609584

Old Hand

Points: 330
More actions
January 12, 2009 at 4:31 pm

#198213

Hi,
While running maintenance plan for reoranizing the index for user database we are getting the following error
"Failed:(-1073548784) Executing the query "ALTER INDEX [PK__EventStage__46AF6B36] ON [Event].[EventStage] REORGANIZE WITH ( LOB_COMPACTION = OFF )
" failed with the following error: "Cannot find index 'PK__EventStage__46AF6B36'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
Please help us to resolve this issue.
The error occurs while reorganizing the index for operations manager database.
Regards,
Karthikraj.L

Viewing 14 posts - 1 through 13 (of 13 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please run the following query in that database. What does it return?

SELECT count(*) from sys.objects

where name = 'PK__EventStage__46AF6B36'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Abhijit More SSCertifiable Points: 6897 More actions · Answer 2

Also verify whether index exists or not from below query...

SELECT * FROM sys.indexes WHERE name = 'PK__EventStage__46AF6B36'

Abhijit - http://abhijitmore.wordpress.com

Looser Ivo SSC Journeyman Points: 77 More actions · Answer 3

We have the same Problem. Take a look here http://www.eggheadcafe.com/software/aspnet/30972265/optimization--index-prob.aspx

I removed the OperationsManagerDW from Indexing.

Rob44 Right there with Babe Points: 735 More actions · Answer 4

Let's suppose the command returns 0! What's the next step if the rebuild of the index in the maintenance plan is unsuccessful?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

Rob44 (3/8/2010)
Let's suppose the command returns 0! What's the next step if the rebuild of the index in the maintenance plan is unsuccessful?

We can give manual run to ALTER INDEX [PK__EventStage__46AF6B36] ON [Event].[EventStage] REORGANIZE WITH ( LOB_COMPACTION = OFF )

and see if problem persists or not

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Paul White SSC Guru Points: 150467 More actions · Answer 6

lkarthikraj-609584 (1/12/2009)
Cannot find index 'PK__EventStage__46AF6B36'

That's the problem with relying on system-generated names for constraints, such as primary keys.

You would think Microsoft would know better.

USE tempdb;

GO

CREATE TABLE #SystemGenerated

(

-- No constraint name specified

row_id INTEGER IDENTITY NOT NULL PRIMARY KEY

);

GO

CREATE TABLE #Explicit

(

row_id INTEGER IDENTITY NOT NULL,

-- Explicitly named constraint

CONSTRAINT [PK #Explicit row_id]

PRIMARY KEY CLUSTERED (row_id ASC)

WITH (FILLFACTOR = 100)

ON [PRIMARY]

);

GO

-- Show primary key details

SELECT constraint_name = name,

type_desc,

is_system_named,

table_schema = SCHEMA_NAME([schema_id]),

table_name = OBJECT_NAME(parent_object_id)

FROM sys.key_constraints

WHERE parent_object_id = OBJECT_ID(N'tempdb..#SystemGenerated', N'U')

OR parent_object_id = OBJECT_ID(N'tempdb..#Explicit', N'U');

GO

-- Clean up

DROP TABLE

#SystemGenerated,

#Explicit;

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 7

Bhuvnesh (3/10/2010)
Rob44 (3/8/2010)
Let's suppose the command returns 0! What's the next step if the rebuild of the index in the maintenance plan is unsuccessful?
We can give manual run to ALTER INDEX [PK__EventStage__46AF6B36] ON [Event].[EventStage] REORGANIZE WITH ( LOB_COMPACTION = OFF )
and see if problem persists or not

Hi ,

its working fine while running this on query analyzer. issues coming while running the maintanance plan.

Karthik Pls update if you found solution.

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 8

sudhakara (6/6/2010)
Bhuvnesh (3/10/2010)
Rob44 (3/8/2010)
Let's suppose the command returns 0! What's the next step if the rebuild of the index in the maintenance plan is unsuccessful?
We can give manual run to ALTER INDEX [PK__EventStage__46AF6B36] ON [Event].[EventStage] REORGANIZE WITH ( LOB_COMPACTION = OFF )
and see if problem persists or not
Hi ,
its working fine while running this on query analyzer. issues coming while running the maintanance plan.
Karthik Pls update if you found solution.

Simulated and Fixed: There is a DTS job for creating and dropping the objects in the database everyday at perticular time. This has been found by using the schema change report in sql server 2005 and fixed by changing the job time of the Reorganize index.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

I would say to stop using the maintenance plans for maintenance activities. Use Ola Hallengren's stuff instead. Sooooo much better!

http://ola.hallengren.com/

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

mw_sql_developer SSCoach Points: 19445 More actions · Answer 10

Not sure whether this will be useful or not.

We run our maintenance jobs for indexes at a table level. So the following command will

work for any index associated with a table.

ALTER INDEX ALL ON Table1 reorganize;

Similarly you can also use the following command to rebuild:

ALTER INDEX ALL ON Table1 rebuild;

By the way, I like to know whether there is one single command that will work on all tables

and all indexes ( So that we don't have to run this command against every single table )

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

It's generally not a good idea to rebuild all indexes on all tables on anything other than a tiny database. The log impact and the time required just become too great. I second Kevin's recommendation of a custom script (there are several) to just rebuild the indexes that need rebuilding. It's a waste to rebuild indexes that aren't fragmented.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

free_mascot One Orange Chip Points: 27169 More actions · Answer 12

I was also having the same issue; following link is useful:

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/04/19/db-maintenance-rebuild-index-task-always-fails-on-operationsmanagerdw-scom-database.aspx

There is no need to reindex OperationsManagerDW.

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

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

GilaMonster (6/14/2010)
It's generally not a good idea to rebuild all indexes on all tables on anything other than a tiny database. The log impact and the time required just become too great. I second Kevin's recommendation of a custom script (there are several) to just rebuild the indexes that need rebuilding. It's a waste to rebuild indexes that aren't fragmented.

I have to agree. After a DBA was riffed from a former employer, I discovered when changing a datbase this DBA was responsible for from BULK_LOGGED to FULL recovery model, that is just what he was doing. Every index on every table every night was being rebuilt. This was a 15 GB database, and the log exploded to 30 GB with the change in recovery model.