July 9, 2010 at 2:41 pm
--Can not rebuild index online, but offline i OK...
--Create table
CREATE TABLE [dbo].[QrpImportErrors](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](7000) NULL,
[ThtCode] [int] NULL,
[StatusCode] [int] NULL,
[StatusDescription] [text] NULL,
[RowCreatedDate] [datetime] NULL,
[OperatorId] [int] NULL,
CONSTRAINT [PK_QrpImportErrors] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
--I can REBUILD offline this is result
ALTER INDEX [PK_QrpImportErrors] ON [dbo].[QrpImportErrors] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
--Result: Command(s) completed successfully.
--When I try online, this fails
ALTER INDEX [PK_QrpImportErrors] ON [dbo].[QrpImportErrors] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON )
--Result: Msg 2725, Level 16, State 2, Line 1 -> Online index operation cannot be performed for index 'PK_QrpImportErrors' because the index contains column 'StatusDescription' 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.
What are I missing, can't see text field as part of my index..
Hope some one can help
July 9, 2010 at 2:45 pm
It sounds like it means that if a text column is in the table at all, you can't rebuild online. Will reseach more to confirm.
Which version of SQL?
Scott Pletcher, SQL Server MVP 2008-2010
July 9, 2010 at 3:57 pm
I don't see any restrictions like that in MS docs on online index rebuilds. So that is an odd error.
Scott Pletcher, SQL Server MVP 2008-2010
July 9, 2010 at 7:29 pm
Because the clustered index is the table - and the table has a text column. If the index was a non-clustered index, you could rebuild it online because it would not contain the text column.
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
July 10, 2010 at 2:35 am
Hi
Using SS2005 ENT x64 SP3
My database is only 50GB and index takes 1min to rebuild, but it will crash app using it, and it is a 24x7 app where CLW is every 3 month, this has been accepted by last DBA, but index is highly fragged (80+%), so action is needed.
@Jeffery: So if a table contains a text/ntext/blob and index is clustered, we can not rebuild online, can I then use reorganize to optimize my index.. Offline is not a option. 🙂
Any article/BOL on this?
I had the understanding that a clustered index only contained columns added to it, not all in table, if so no need for a index 😉
Hope on a explanition on this issue
July 10, 2010 at 4:43 am
Jeffrey Williams-493691 (7/9/2010)
Because the clustered index is the table - and the table has a text column. If the index was a non-clustered index, you could rebuild it online because it would not contain the text column.
Precisely. The clustered index contains all columns of the table. One of those columns is a LOB column (in this case text) and it's a clearly documented restriction of online index rebuilds that the index may not contain a LOB column.
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
July 10, 2010 at 4:47 am
scott.pletcher (7/9/2010)
I don't see any restrictions like that in MS docs on online index rebuilds. So that is an odd error.
http://msdn.microsoft.com/en-us/library/ms188388%28SQL.90%29.aspx
Indexes, including indexes on global temp tables, can be rebuilt online with the following exceptions:
* Disabled indexes
* XML indexes
* Indexes on local temp tables
* Partitioned indexes
* Clustered indexes if the underlying table contains LOB data types
* Nonclustered indexes that are defined with LOB data type columns
Nonclustered indexes can be rebuilt online if the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey columns.
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
July 10, 2010 at 2:43 pm
Thanks for all the answers ...
I now know more on this topic, allways nice to learn new stuff
July 10, 2010 at 2:53 pm
SpiderDK (7/10/2010)
Any article/BOL on this?I had the understanding that a clustered index only contained columns added to it, not all in table, if so no need for a index 😉
The clustered index is the table, at the leaf level of the clustered index you find the actual data pages of the table.
Might make things clearer - http://www.sqlservercentral.com/articles/Indexing/68563/
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
July 12, 2010 at 7:57 am
Note that you can still defragment the index "live", you just can't fully rebuild it.
Scott Pletcher, SQL Server MVP 2008-2010
July 14, 2010 at 7:18 am
Is this by making a reorganize instead? Or how?
July 14, 2010 at 7:43 am
Yes. The Reorganise operation is always online, there's no specific option that you need to specify.
It's not as good as rebuilding the index, especially with high fragmentation, but better than nothing.
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
July 14, 2010 at 1:00 pm
But in this case the best I can do 🙂
And then order a CLW on one DB at time..
Thanks for all the advises
July 14, 2010 at 2:04 pm
If you've got a lot of old data, you could partition the db so that new data was separate from old data. That could reduce the new partition size enough to let you rebuild it.
You could, of course, rebuild each of the old data partitions as well, presumably only once (since the old data won't be changing enough to need rebuilt again).
Of course, that requires you to have the time to do the partitioning :ermm:
Scott Pletcher, SQL Server MVP 2008-2010
July 15, 2010 at 12:56 am
Hi thanks for the last advise,
Have one DB on 200+ GB and rest is small around 10-50GB, I have looked at partioning, but I'm hosted in a SAN and another LUN would be on same physical disks, so can not see any gain in spending the hours...
I have no performance issue right now, but I'm looking at optimmzions becourse it will be a issue later
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply