| Author |
Topic |
|
DBA007
Posting Yak Master
145 Posts |
 Posted - 2010-02-01 : 11:48:20
|
| 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. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-01 : 12:13:09
|
| As per usual such JOBs tell us nothing / little about the problem.What happens if you rebuild the indexes manually, what message do you see then? |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-02-01 : 13:21:55
|
| This does not give any error . I would recommend , you put a "print" on your script , and save to a Log file - configure this in the Advanced section of your joib step. When the job runs you should get a print out of of all the attempted ALTER INDEX statements , and the failures.Jack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-01 : 13:26:39
|
"on your script"I think this is probably Wizard-generated  |
|
|
|
DBA007
Posting Yak Master
145 Posts |
Posted - 2010-02-01 : 13:37:18
|
| no,this job was not wizard generated,it was written using TSQL |
|
|
|
DBA007
Posting Yak Master
145 Posts |
Posted - 2010-02-01 : 13:51:21
|
| HI,in the file i got the message for all indexes as followsExecuted: 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] |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-01 : 14:04:51
|
| "no,this job was not wizard generated,it was written using TSQL"OK, that's good, makes it easy to re-run "manually" to see any messages - and, indeed, to modify to Log "progress" or "debugging" messages.You probably have a Computed Column in that table / index.I find Computed Columns a PITA in so many ways, like this, that we now avoid them.I don't know the deal here; my guess is that either the Index should have been created with SET QUOTED_IDENTIFIER ON, or the re-indexing SProc should have been created with that state. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-02-01 : 14:18:19
|
| Put the following at the start of your scriptSET ARITHABORT ON SET QUOTED_IDENTIFIER ON Jack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-01 : 14:32:42
|
| "Put the following at the start of your script"The index rebuild script?or the CREATE INDEX script?or the CREATE Computed Column script?I have never understood what QUOTED_IDENTIFIER is supposed to achieve, and when it is supposed to be used. |
|
|
|
DBA007
Posting Yak Master
145 Posts |
Posted - 2010-02-01 : 14:40:48
|
| thanks jackv,the job has executed successfully but on the txt file for every index i got the messages as follows.Executed: ALTER INDEX [PK_User] ON [dbo].[one] REORGANIZE [SQLSTATE 01000] what is this [SQLSTATE 01000] ? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-01 : 16:31:15
|
| "the job has executed successfully"Has it? You posted an error message above?? or did you fix that? and if so I'd be interested to know what change you made? |
|
|
|
DBA007
Posting Yak Master
145 Posts |
Posted - 2010-02-01 : 16:51:19
|
| I mean the job was executed,with out showing any errors,but on the txt file,I get these statements for all the indexes in the database asExecuted: ALTER INDEX [IX_Group] ON [dbo].[Quoted] REBUILD [SQLSTATE 01000] |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-01 : 16:53:24
|
| I expect they are benign.Did you manage to fix "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"? |
|
|
|
DBA007
Posting Yak Master
145 Posts |
Posted - 2010-02-01 : 16:58:54
|
| no, I have added the QUOTED_IDENTIFIER,ARITHABORT to set to ON |
|
|
|
DBA007
Posting Yak Master
145 Posts |
Posted - 2010-02-01 : 17:30:39
|
| the indexes have not rebild or reorganized,but with the messages as [SQLSTATE 01000] for all the rebuild and reorganize. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-02-02 : 03:34:44
|
| As far as I know when the [SQLSTATE 01000] is part of the print functionalityJack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-02-02 : 03:38:30
|
| The QUOTED_IDENTIFIER force the script to follow rules regarding identifiers and literals. The ARITHABORT deals with the divide by zeroJack Vamvas--------------------http://www.ITjobfeed.com |
|
|
|
|
Reorg,Rebuild index job failed