Index optimization failed for User databases

  • 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

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

  • 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
  • hi ,

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

  • 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).

  • 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 '-'??

  • 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 Lavanya,

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

  • 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 ' - ' ???

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

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

    Jayanth Kurup[/url]

  • 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

  • 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
  • Sorry Gila,

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

    I need to execute the script manually or ???

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

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

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