Index optimization failed for User databases

Question

Post reply

Index optimization failed for User databases

Lavanyasri

SSCarpal Tunnel

Points: 4907
More actions
January 2, 2013 at 2:36 am

#287123

Hi ,
Index optimization job is failing with the below error ::
Message
Executed as user: XXXXXXXXX. Incorrect syntax near '-'. [SQLSTATE 42000] (Error 102). The step failed.
Thanks,
lavanya

Viewing 15 posts - 1 through 15 (of 16 total)

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

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

Check the code used in the job step. Not enough info to really say much more.

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

You have a table or index with a - in the name and your code doesn't wrap table and index names in []. You're just going to have to go through the job, see where the incorrect line of code is and fix it.

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

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 3

hi ,

we are using the same script in all servers ,but wea re not seeing any issues on other servers expect this server ..

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

You have index(s) with a - in their name. If the script does not put the index name in [] brackets when rebuilding/reorganising, then the script will fail.

Either change the index(s) name, or change the script to wrap [] around the index(s).

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 5

i need to change the script in the below lines

IF @frag < 30.0

SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' +@DBname+ N''.''+ @schemaname + N''.'' + @objectname + N'' REORGANIZE'';

IF @frag >= 30.0

SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' +@DBname+ N''.'' + @schemaname + N''.'' + @objectname + N'' REBUILD'';

IF @partitioncount > 1

SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10));

EXEC (@command);

-- PRINT N''Executed: '' + @command;

END;

how can i find the index starts with '-'??

Ant-Green SSC Guru Points: 113507 More actions · Answer 6

Check sys.indexes in every database where the name has -

IF @frag < 30.0

SET @command = N''ALTER INDEX ['' + @indexname + N''] ON ['' +@DBname+ N''].[''+ @schemaname + N''].['' + @objectname + N''] REORGANIZE'';

IF @frag >= 30.0

SET @command = N''ALTER INDEX ['' + @indexname + N''] ON ['' +@DBname+ N''].['' + @schemaname + N''].['' + @objectname + N''] REBUILD'';

IF @partitioncount > 1

SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10));

EXEC (@command);

-- PRINT N''Executed: '' + @command;

END;

Added extra [] around the db schema object in case anything else has special characters in the name.

Shadab Shah SSCarpal Tunnel Points: 4759 More actions · Answer 7

Hi Lavanya,

Did the above mention code solved your Problem or are you still facing it?

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 8

hi Thanks for the quick response ..

I searched alldatabases with the below script , but i didnt find any indexes names with -

select * from sys.indexes where name like '%-%'

i have a doubt you are saying about underscore' _' or ' - ' ???

Ant-Green SSC Guru Points: 113507 More actions · Answer 9

If no indexes with a - then do you have a database or a schema or an object which has - in its name?

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 10

copy paste the content of @command into SSMS and then do a syntax check

Jayanth Kurup[/url]

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 11

anthony.green (1/2/2013)
Check sys.indexes in every database where the name has -
IF @frag < 30.0
SET @command = N''ALTER INDEX ['' + @indexname + N''] ON ['' +@DBname+ N''].[''+ @schemaname + N''].['' + @objectname + N''] REORGANIZE'';
IF @frag >= 30.0
SET @command = N''ALTER INDEX ['' + @indexname + N''] ON ['' +@DBname+ N''].['' + @schemaname + N''].['' + @objectname + N''] REBUILD'';
IF @partitioncount > 1
SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
-- PRINT N''Executed: '' + @command;
END;
Added extra [] around the db schema object in case anything else has special characters in the name.

Hi Anthony,

Thanks for the update........

The above script is worked for me and job ran successfully in one server . I did same changes in another server (index optimization job),but the job was failing with the below error after changes also....

Mensaje

Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near'('. [SQLSTATE 42000] (Error 102). The step failed.

Thanks

Lavanya

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

Print the command (rather than executing it) and see what it returns.

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

Lavanyasri SSCarpal Tunnel Points: 4907 More actions · Answer 13

Sorry Gila,

i am not getting you .. can you please explain more ...

I need to execute the script manually or ???

Ant-Green SSC Guru Points: 113507 More actions · Answer 14

Instead of exec (@command), do print @command, then you can see where the failing object is.