July 25, 2008 at 10:53 am
Hello good folks of sqlservercentralville!! 😀
This problem is part of my Weekly maintenance job. There is this particular clustered index that keeps failing to rebuild (and is actually holding up the rest of the reindexing operations on the rest of the tables). I am NOT indexing the text column(comments) but the error message keeps saying that I am trying to index a text column.
[highlight]
Actual Error Message:
Msg 2725, Level 16, State 2, Line 10
Online index operation cannot be performed for index 'CIX_STG_tblVouchers_DateCompleted'
because the index contains column 'Comments' 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.
[/highlight]
How can I fix this issue? and let SQL Server 2005 rebuild the index.
or How can you skip this table during the general Reindexing operation.
/*** Operations that is causing the above error: ***/
ALTER INDEX [CIX_tblVoucher_DateCompleted] ON [dbo].[tblVoucher]
REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON )
/*** Create index statement: ***/
CREATE CLUSTERED INDEX [CIX_tblVoucher_DateCompleted] ON [dbo].[tblVoucher]
(
[DateCompleted] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
I am using SQLServer 2005 std sp2.
Please let me know if you guys need any further information regarding this problem.
your help with this is very much appreciated.
Shiva
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 25, 2008 at 12:08 pm
Since it's the clustered index, it actually includes all the data in the column, technically.
You'll have to make that rebuild be "offline". Just change where it says, "ONLINE = ON" to "ONLINE = OFF".
That might affect how the table gets accessed during the rebuild. Is that during an idle time for the database or is it busy at that time?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 25, 2008 at 12:54 pm
Hi GSquared, thanks for the response.
This happens during an idle time, 3:00 am.
But I cant change the script for this alone, its automatically generated as a part of Rebuild index task in SSIS. If I unckeck the 'Keep index online while reindexing', it will effect every other table on the instance.
Is there anyway i can do it for this table alone?
On second thought, I can actually disable the 'Keep index online while reindexing' flag for all the tables as this is during off hours.
I will try this and see how it works.
Does taking the index offline make the reindexing any faster?
Thanks
shiva
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 25, 2008 at 2:26 pm
I have a stored procedure that you could use to dynamically rebuild your indexes online or offline. In your case the clustered index on the table with a LOB column, would be rebuilt offline, all other indexes would be rebuilt online.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html.
Ola Hallengren
July 25, 2008 at 3:20 pm
Thank you Ola Hallengren...
I will check it out.
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 28, 2008 at 2:03 pm
Taking indexing offline mainly reduces the hit on tempdb. I don't think it makes any particular speed difference, but I haven't tested that so can't be sure. Certainly won't make it take longer.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 28, 2008 at 2:36 pm
Thanks GSquared!!
so, its like a copy of the existing index is created on tempdb while the original index is being reindexed in online mode?
thats why you see the tempdb activity?
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 29, 2008 at 7:06 am
Yep.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 29, 2008 at 8:17 am
Thanks for your help GSquared!
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply