<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
Reindexing job is failing
11 posts, Page 1 of 2
1
2
»»
Reindexing job is failing
Rate Topic
Display Mode
Topic Options
Author
Message
Simha24
Simha24
Posted Tuesday, April 24, 2012 1:10 AM
SSC 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
anthony.green
anthony.green
Posted Tuesday, April 24, 2012 1:23 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
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
Suresh B.
Suresh B.
Posted Tuesday, April 24, 2012 1:47 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:59 AM
Points: 1,075,
Visits: 5,114
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
Simha24
Simha24
Posted Tuesday, April 24, 2012 2:41 AM
SSC 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
(
17 views,
8.56 KB
)
Post #1288726
anthony.green
anthony.green
Posted Tuesday, April 24, 2012 2:47 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
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
Simha24
Simha24
Posted Tuesday, April 24, 2012 3:09 AM
SSC 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
anthony.green
anthony.green
Posted Tuesday, April 24, 2012 3:13 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
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
Simha24
Simha24
Posted Tuesday, April 24, 2012 3:46 AM
SSC 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
Simha24
Simha24
Posted Tuesday, April 24, 2012 4:20 AM
SSC 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
anthony.green
anthony.green
Posted Tuesday, April 24, 2012 4:22 AM
SSCertifiable
Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075,
Visits: 4,831
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 »
11 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
