May 28, 2009 at 7:11 pm
It's me again.
I have a reindex job thats failing I haven't been able to figure out why. It states that the index includes a column "tuningoptions". All I can find on the web for the error if info about the Tuning Advisor.
Thanks for any help.
Output from the jobs log file.
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
Report was generated on "myserver".
Maintenance Plan: System Db's
Duration: 00:01:30
Status: Warning: One or more tasks failed..
Details:
Rebuild Index (myserver)
Rebuild index on Local server connection
Databases: All system databases
Object: Tables and views
Original amount of free space
Task start: 2009-05-28T15:48:16.
Task end: 2009-05-28T15:48:16.
Failed:(-1073548784) Executing the query "ALTER INDEX [PK__DTA_input__009508B4] ON [dbo].[DTA_input] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON )
" failed with the following error: "Online index operation cannot be performed for index 'PK__DTA_input__009508B4' because the index contains column 'TuningOptions' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Keith
-.- ..-. --... . -..- ---
SELECT * FROM management WHERE clue > 0;
May 28, 2009 at 8:53 pm
You've got to be kidding, right?
failed with the following error: "Online index operation cannot be performed for index 'PK__DTA_input__009508B4' because the index contains column 'TuningOptions' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline."
The error message tells you exactly what the problem is. You are trying to rebuild an index online that cannot be rebuilt using the online feature. The solution is simple - don't rebuild that index online.
The other option you have is to remove that table from your database. Then, you won't have a problem with rebuilding that index because it won't exist anymore.
However, if you have any other table that has any of the referenced data types (text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml) and the table has a clustered index - that clustered index cannot be rebuilt online and must be rebuilt offline.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 29, 2009 at 2:17 am
I'm not sure that you should be trying to rebuild indexes in the system databases.
MSDB maybe, but not the others.
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
May 29, 2009 at 5:28 pm
Jeffrey Williams (5/28/2009)
You've got to be kidding, right?
Sorry Jeffrey if my SQL skills are not up to par with yours. We all have to start somewhere. I don't make changes to things without trying to understand 1. What I'm doing 2. What fallout the change might cause.
I'm a new SQL admin so bare with me.
I have a few other DB's that get is same type of error when a reindex job runs on them. Is there a way to exclude these indexes from being reindexed online and let all the other indexes get reindexed online. Then create another job to offline reindex those indexes that cant be reindexed online?
How are these type of indexes normally handled?
Thanks
Keith
-.- ..-. --... . -..- ---
SELECT * FROM management WHERE clue > 0;
May 29, 2009 at 9:31 pm
MCHPIS (5/29/2009)
Sorry Jeffrey if my SQL skills are not up to par with yours. We all have to start somewhere. I don't make changes to things without trying to understand 1. What I'm doing 2. What fallout the change might cause.I'm a new SQL admin so bare with me.
If you had a problem understanding what the message stated, why didn't you state that? Instead, you stated you could not figure what the problem was. I was remarking on the fact that your error message stated exactly what the problem was.
Now, as to your question on how you can handle this type of issue - there are several options. Which option you take is going to depend upon what is going to work for you and your organization.
The easiest option is to un-check the online option and rebuild all indexes offline. The downside to this option is that during the index rebuild, that index will not be available - and, if that index is the clustered index the table will not be available. This may not be a problem if there are no users on the system during your maintenance window.
Other options include using a script to select the appropriate indexes and identify which indexes can be build online or need to be built offline. SQL Fool has a good script here: http://sqlfool.com/2009/03/automated-index-defrag-script/. You would replace the Rebuild Index Task with an Execute SQL Task that executes this script/procedure with the appropriate parameters.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply