Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Reindexing job is failing Expand / Collapse
Author
Message
Posted Tuesday, April 24, 2012 1:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:45 AM
Points: 94, Visits: 319
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.


Post #1288686
Posted Tuesday, April 24, 2012 1:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
can you post the DDL statements for all objects involved, tables procedures triggers etc



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1288689
Posted Tuesday, April 24, 2012 1:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:23 AM
Points: 1,101, Visits: 5,288
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
Post #1288698
Posted Tuesday, April 24, 2012 2:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:45 AM
Points: 94, Visits: 319
Please check i have attached complete procedure.

Thanks in advance


  Post Attachments 
Reindexprocedure.txt (19 views, 8.56 KB)
Post #1288726
Posted Tuesday, April 24, 2012 2:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
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




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1288733
Posted Tuesday, April 24, 2012 3:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:45 AM
Points: 94, Visits: 319
select max(len(name)) from sys.databases

Result
32
Post #1288740
Posted Tuesday, April 24, 2012 3:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
Change the DBName in the temp table to a data type of SYSNAME this will then eliminate this error from happening.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1288743
Posted Tuesday, April 24, 2012 3:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:45 AM
Points: 94, Visits: 319
Thanks for providing....

i will update u
Post #1288754
Posted Tuesday, April 24, 2012 4:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:45 AM
Points: 94, Visits: 319
i understand the issue...now the problem is resloved....


Thanks for your guidence
Post #1288769
Posted Tuesday, April 24, 2012 4:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
happy to help



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1288773
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse