Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Instead of using my old school index rebuild script I had decided to use 2008's built-in Maintenance Plan to do a simple Rebuild Index task.

The details are:

  • Connection = Local server connection
  • Databases = Specific databases (just one)
  • Object = Tables and Views
  • Free space options = I have tried both default amount and change percentage to 70%
  • Advanced options = Keep index online while reindexing is the only one checked. This is the primary reason I wanted to use this was to see if we could keep an index online while still doing the rebuild.

When I run the T-SQL from this manually everything works ok, but I assume it is not keeping the index online. Whenever I run the job though I get this very strange error that has not led me anywhere:

Executing the query "ALTER INDEX [Agreement_PK] ON [dbo].[Agreement] RE..." failed with the following error: "String or binary data would be truncated. The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

USE [<dbase>]
GO
ALTER INDEX [Agreement_PK] ON [dbo].[Agreement] REBUILD PARTITION = ALL WITH ( FILLFACTOR = 30, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF ) 
GO
USE [<dbase>] 
GO
ALTER INDEX [IX_Agreement_1] ON [dbo].[Agreement] REBUILD PARTITION = ALL WITH ( FILLFACTOR = 30, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF ) 
GO

The T-SQL is very generic and gives no indication as to what isn't set correctly or why such a simple local connection would be a problem. EDIT: Here is that code from my comment.

It is set to use Windows NT Integrated security, but I am not sure why that would be a problem again on a local system connection.

share|improve this question
    
What does the schema of the Agreement table look like? –  mrdenny Sep 29 '11 at 19:23
add comment

1 Answer 1

You have "SET ANSI_WARNINGS OFF" when running the code as T_SQL

When running the job this setting must be different.

share|improve this answer
    
Ummm I don't think so. Here is an example of the code: USE [<dbase>] GO ALTER INDEX [Agreement_PK] ON [dbo].[Agreement] REBUILD PARTITION = ALL WITH ( FILLFACTOR = 30, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF ) GO USE [<dbase>] GO ALTER INDEX [IX_Agreement_1] ON [dbo].[Agreement] REBUILD PARTITION = ALL WITH ( FILLFACTOR = 30, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF ) GO –  maczealot Sep 29 '11 at 15:29
    
@maczealot - Post this code into your question if it is relevant. There we can read it better. :) Also, I do not see any explicit ANSI_WARNINGS setting here, so your job may be defaulting to OFF as Jimbo suggested. What happens when you add SET ANSI_WARNINGS ON; to the beginning of your code? –  Nick Chammas Sep 29 '11 at 21:43
    
Ok, I put that text into my OP and tried running the T-SQL with a SET ANSI_WARNINGS ON; at the beginning and as usual it all completed successfully with (1 row(s) affected) etc. Even if I uncheck the keep-index-online option in the MP it still fails with the "String or binary data would be truncated" thanks again for looking at this problem and for any guidance! –  maczealot Oct 6 '11 at 16:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.