February 1, 2010 at 9:44 am
Team,
My Rebuild,Reorganize index job has been failing because of the following error,it is scheduled to be executed in only one db and the db is in simple mode.
ON [my].[User] REBUILD [SQLSTATE 01000] (Message 0) Executed: ALTER INDEX [IX_myUser_UserID] ON [my].[User] REBUILD [SQLSTATE 01000] (Message 0) Executed: ALTER INDEX [IX_EmPID] ON [EMPSs].[EMP] REORGANIZE [SQLSTATE 01000] (Message 0) Executed: ALTER INDEX [idx_TypeID] ON [EMPs].[EMP] REORGANIZE [SQLSTATE 01000] (Message 0) Executed: ALTER INDEX [IX_Files] ON [dbo].[Unload] REBUILD [SQLSTATE 01000] (Message 0) Executed: ALTER INDEX [IX_CardID] ON [Card].[Time] REBUILD [SQLSTATE 01000] (Message 0) Executed: ALTER INDEX [PK_PowerID] ON [dbo].[Power] REBUILD [SQLSTATE 01000] (Message 0) Executed: ALT... The step failed.
February 1, 2010 at 10:14 am
dba_neo (2/1/2010)
Team,My Rebuild,Reorganize index job has been failing because of the following error,it is scheduled to be executed in only one db and the db is in simple mode.
ON [my].[User] REBUILD [SQLSTATE 01000] (Message 0) Executed: ALTER INDEX [IX_myUser_UserID] ON [my].[User] REBUILD [SQLSTATE 01000] (Message 0) Executed: ALTER INDEX [IX_EmPID] ON [EMPSs].[EMP] REORGANIZE [SQLSTATE 01000] (Message 0) Executed: ALTER INDEX [idx_TypeID] ON [EMPs].[EMP] REORGANIZE [SQLSTATE 01000] (Message 0) Executed: ALTER INDEX [IX_Files] ON [dbo].[Unload] REBUILD [SQLSTATE 01000] (Message 0) Executed: ALTER INDEX [IX_CardID] ON [Card].[Time] REBUILD [SQLSTATE 01000] (Message 0) Executed: ALTER INDEX [PK_PowerID] ON [dbo].[Power] REBUILD [SQLSTATE 01000] (Message 0) Executed: ALT... The step failed.
Did you try to run it from SQL Manage Studio manually out of the job? I will give you more specific error message if any...
February 1, 2010 at 10:22 am
No,i didnt run the job manually ,it has been scheduled as a job!
February 1, 2010 at 10:50 am
can any know the solution for this problem?
February 1, 2010 at 11:52 am
HI,
i got the message for all indexes as follows
Executed: ALTER INDEX [IX_ActGUID] ON [dbo].[Primt] REBUILD [SQLSTATE 01000]
Msg 1934, Sev 16, State 1, Line 1 : ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. [SQLSTATE 42000]
February 1, 2010 at 1:06 pm
dba_neo (2/1/2010)
HI,i got the message for all indexes as follows
Executed: ALTER INDEX [IX_ActGUID] ON [dbo].[Primt] REBUILD [SQLSTATE 01000]
Msg 1934, Sev 16, State 1, Line 1 : ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. [SQLSTATE 42000]
This means that you have one or more indexes that have been created on views (or computed) columns with incorrect settings.
Identify those indexed views and recreate them with the appropriate SET options for indexed views. You can find the correct settings in books online.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 1, 2010 at 3:10 pm
how can i identify the computed views/columns and rebuild them using set operation?
February 1, 2010 at 6:02 pm
select distinct object_name(syscolumns.id),
syscolumns.name As CalculatedColumn ,
syscomments.text as TheCalculation,
isnull(object_name(SYSINDEXKEYS.id),'No Index Using This CalculatedColumn') As IndexName
from syscolumns
inner join syscomments on syscolumns.id=syscomments.id
left outer join sysindexes
on syscolumns.id=sysindexes.id
left outer join SYSINDEXKEYS ON sysindexes.ID=SYSINDEXKEYS.ID
AND sysindexes.INDID=SYSINDEXKEYS.INDID
and SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID
where iscomputed <> 0
http://www.sqlservercentral.com/Forums/Topic839504-146-1.aspx#bm840446
MJ
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply