Reindexing job is failing

  • Hi All,

    When i am executing DB Reindex in pre production environment..this job is getting failled and writing bellow error in history but same job is exeuting successfully in local.

    Message

    Executed as user: VOCPP\svc_visqlv02-pp-me_s. SQL ReIndexing Script Version 1.2 [SQLSTATE 01000] (Message 0) @DBName="" [SQLSTATE 01000] (Message 0) @ObjectName="" [SQLSTATE 01000] (Message 0) @HighFragLevel="10" [SQLSTATE 01000] (Message 0) @MinFragLevel="10" [SQLSTATE 01000] (Message 0) @ReOrganizeLevel="30" [SQLSTATE 01000] (Message 0) ReIndex_History Table Does Exist [SQLSTATE 01000] (Message 0) ReIndexing Started At Apr 24 2012 7:40AM [SQLSTATE 01000] (Message 0) String or binary data would be truncated. [SQLSTATE 22001] (Error 8152) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

    Msg 8152, Level 16, State 2, Procedure sp_ReIndex, Line 77

    String or binary data would be truncated.

  • can you post the DDL statements for all objects involved, tables procedures triggers etc

  • Simha24 (4/24/2012)


    Msg 8152, Level 16, State 2, Procedure sp_ReIndex, Line 77

    String or binary data would be truncated.

    Please check Line 77 of procedure sp_ReIndex

  • Please check i have attached complete procedure.

    Thanks in advance

  • what is the largest name of a database on the instance?

    on first glance it looks that you have a DB name larger than 25 and its failing inserting into the temp table

    select max(len(name)) from sys.databases

  • select max(len(name)) from sys.databases

    Result

    32

  • Change the DBName in the temp table to a data type of SYSNAME this will then eliminate this error from happening.

  • Thanks for providing....

    i will update u

  • i understand the issue...now the problem is resloved....

    Thanks for your guidence

  • happy to help

  • is it the same for Index name? because I received the same error but my DB name is not more than 32 but index name is 64 and 47?

Viewing 11 posts - 1 through 10 (of 10 total)

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