?
Solved

Online Reindexing MSSQL 2005

Posted on 2006-11-05
12
Medium Priority
?
1,798 Views
Last Modified: 2010-11-10
We've recently upgraded to MSSQL 2005 Enterprise.  One feature we'd like to use is the online indexing for our maintenance.  It's simple to make the plan with the wizards, just put a check in the 'reindex online' box.  But, when running it fails.  We get:

Failed:(-1073548784) Executing the query "ALTER INDEX [PK_AddressBook] ON [dbo].[AddressBook] 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_AddressBook' because it contains column 'EmailToLong' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. 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.

Well.. I understand that it can't do the online reindex for columns of type 'text' but how do I have a plan that will just do everything online that it can, and then if it needs to take it offline do so?  Certainly I could do each table one at a time, but I want to leverage the maintenance plan of, "All databases, reindex all tables" so as I add tables or databases I don't need to alter my plan manually.  Any insight would be great.
0
Comment
Question by:skacore
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
12 Comments
 
LVL 26

Expert Comment

by:DireOrbAnt
ID: 17876807
Just pick the indexes that don't have large datatypes in them and start the online ALTER for those.
Then schedule the ones that do have those field types.

I don't believe you can automate the mix using the maintenance plan.
0
 
LVL 1

Author Comment

by:skacore
ID: 17876831
Just a follow up.  How would I 'pick' the ones?  Do I really need to write out..

ALTER INDEX [PK_blah] for each index?

I've got a lot of tables/indexes/databases to deal with.  :)
0
 
LVL 26

Expert Comment

by:DireOrbAnt
ID: 17876873
Well, you should not have too many indexes (if any) on text, images...
I would start there. Why do you need to index these fields? You might want to use Full-Text indexes for these fields or just drop the index.
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 1

Author Comment

by:skacore
ID: 17876878
You know, that's a good point... :)
0
 
LVL 1

Author Comment

by:skacore
ID: 17876884
However, if I do need to keep these indexes on (due to say.. higher ups not letting me take them off) I'd be interested in an automated solution.
0
 
LVL 26

Accepted Solution

by:
DireOrbAnt earned 1000 total points
ID: 17877109
I don't have an automated solution, maybe others do.
Now, I can't imagine that most tbales have indexes on text/binary fields! Is it like that?

My recommendation would be to look at any table with indexes on those field types and ask others why those are there.
Removing them will reduce space used, will speed up inserts and such and will most likely speed up other queries.

If LIKE 'start%' is used on those fields, then you can replace those indexes with a FT index.
0
 
LVL 1

Author Comment

by:skacore
ID: 17878639
Alrighty.. I agree about the index on a text field.  It really makes no sense.  So I went to the table to remove it.. and there isn't an index on that column.  The only index on the table is PK_AddressBook and that is just the index for the prime key which is just an int.  However.. it is a clustered index, perhaps that is the problem?  I'm not sure really if it should be clustered or not.  Any advice?

0
 
LVL 26

Expert Comment

by:DireOrbAnt
ID: 17878724
Odd, is EmailToLong a field on that table?
0
 
LVL 1

Author Comment

by:skacore
ID: 17880872
Yes it is.  It is a field of datatype 'text' as well.  Odd indeed!
0
 
LVL 1

Author Comment

by:skacore
ID: 17988321
Hello,
  I've been working on this issue for a while.  Here's what I've found out.  If you have a clustered index (say primekey) and a column in that table is of type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml then the redindex dies.  I'd like to come up with a way for SQL to reindex tables that it can online, online, and to do the others offlines.  This is what I've gotten for a script so far:

DECLARE @TableName varchar(255)
DECLARE @SQL nvarchar(4000)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table' --AND table_name NOT IN  ('MyTableName')

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @TableName
set @SQL='ALTER INDEX  ALL ON '+ @TableName+' REBUILD WITH (ONLINE  = ON)'
EXEC sp_executeSQL @SQL
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor


This does work, but I have to manually add the tables of type text to it.  I'm sure there must be a way I can sub query that to find the tables of the offending types, and then to just do those offline = off.  Any thoughts on that?
0
 
LVL 1

Author Comment

by:skacore
ID: 17990109
Alrighty, I've come up with this, and it looks like it works.  It will do online index any table that it can for all dbs except for tempdb, and then it will do all the other ones in an offline fashion.  Note, I have the 2 lines that execute commented out, and it just prints the sql that it would execute so you can test it.

DECLARE @TableName varchar(255)
DECLARE @DatabaseName varchar(255)
DECLARE @SQL nvarchar(4000)
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name <> 'tempdb'
ORDER BY name

OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN

      set @SQL = 'DECLARE TableCursorOnline CURSOR FOR SELECT DISTINCT(TABLE_NAME) FROM ' + @DatabaseName + '.information_schema.tables
      WHERE table_type = ''base table''
      AND TABLE_NAME NOT IN
      (SELECT DISTINCT(a.TABLE_NAME) FROM ' + @DatabaseName + '.information_schema.tables a
      JOIN ' + @DatabaseName + '.Information_Schema.Columns b
      on a.TABLE_NAME = b.TABLE_NAME
      WHERE a.table_type = ''base table''
      AND (b.DATA_TYPE = ''text''
      OR b.DATA_TYPE = ''ntext''
      OR b.DATA_TYPE = ''image''
      OR b.DATA_TYPE = ''varchar(max)''
      OR b.DATA_TYPE = ''nvarchar(max)''
      OR b.DATA_TYPE = ''varbinary(max)''
      OR b.DATA_TYPE = ''xml'')
      )'
      EXEC sp_executeSQL @SQL
      
      set @SQL = 'DECLARE TableCursorOffline CURSOR FOR SELECT DISTINCT(a.TABLE_NAME) FROM ' + @DatabaseName + '.information_schema.tables a
      JOIN ' + @DatabaseName + '.Information_Schema.Columns b
      on a.TABLE_NAME = b.TABLE_NAME
      WHERE a.table_type = ''base table''
      AND (b.DATA_TYPE = ''text''
      OR b.DATA_TYPE = ''ntext''
      OR b.DATA_TYPE = ''image''
      OR b.DATA_TYPE = ''varchar(max)''
      OR b.DATA_TYPE = ''nvarchar(max)''
      OR b.DATA_TYPE = ''varbinary(max)''
      OR b.DATA_TYPE = ''xml'')'
      EXEC sp_executeSQL @SQL
            
      OPEN TableCursorOnline
      FETCH NEXT FROM TableCursorOnline INTO @TableName
      WHILE @@FETCH_STATUS = 0
      BEGIN
            --PRINT 'Reindexing ' + @TableName
            set @SQL='ALTER INDEX ALL ON '+ @DatabaseName + '.' + @TableName+' REBUILD WITH (ONLINE  = ON)'
            --EXEC sp_executeSQL @SQL
            PRINT @SQL
            FETCH NEXT FROM TableCursorOnline INTO @TableName
      END
      CLOSE TableCursorOnline
      DEALLOCATE TableCursorOnline
      
      OPEN TableCursorOffline
      FETCH NEXT FROM TableCursorOffline INTO @TableName
      WHILE @@FETCH_STATUS = 0
      BEGIN
            --PRINT 'Reindexing ' + @TableName
            set @SQL='ALTER INDEX ALL ON '+ @DatabaseName + '.' + @TableName+' REBUILD WITH (ONLINE  = OFF)'
            --EXEC sp_executeSQL @SQL
            PRINT @SQL
            FETCH NEXT FROM TableCursorOffline INTO @TableName
      END
      CLOSE TableCursorOffline
      DEALLOCATE TableCursorOffline
      FETCH NEXT FROM DatabaseCursor INTO @DatabaseName
END
CLOSE DatabaseCursor    
DEALLOCATE DatabaseCursor
0
 
LVL 1

Author Comment

by:skacore
ID: 17996560
Whoops! How about one that works?  Here it is as a stored proc:

--reindex all, online when possible

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[usp_rebuildindex]
AS
BEGIN
      SET NOCOUNT ON;
--rebuild all indexes online if you can for all dbs, offline otherwise
DECLARE @TableName varchar(255)
DECLARE @DatabaseName varchar(255)
DECLARE @SQL nvarchar(4000)
DECLARE DatabaseCursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name <> 'tempdb'
ORDER BY name

OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN

      set @SQL = 'DECLARE TableCursorOnline CURSOR FOR SELECT DISTINCT(TABLE_NAME) FROM ' + @DatabaseName + '.information_schema.tables
      WHERE table_type = ''base table''
      AND TABLE_NAME NOT IN
      (SELECT DISTINCT(a.TABLE_NAME) FROM ' + @DatabaseName + '.information_schema.tables a
      JOIN ' + @DatabaseName + '.Information_Schema.Columns b
      on a.TABLE_NAME = b.TABLE_NAME
      WHERE a.table_type = ''base table''
      AND (b.DATA_TYPE = ''text''
      OR b.DATA_TYPE = ''ntext''
      OR b.DATA_TYPE = ''image''
      OR b.DATA_TYPE = ''xml''
      OR (
            (b.DATA_TYPE = ''varchar''
            OR b.DATA_TYPE = ''nvarchar''
            OR b.DATA_TYPE = ''varbinary'')
            AND
            b.CHARACTER_MAXIMUM_LENGTH = -1)
      ))'
      EXEC sp_executeSQL @SQL
      
      set @SQL = 'DECLARE TableCursorOffline CURSOR FOR SELECT DISTINCT(a.TABLE_NAME) FROM ' + @DatabaseName + '.information_schema.tables a
      JOIN ' + @DatabaseName + '.Information_Schema.Columns b
      on a.TABLE_NAME = b.TABLE_NAME
      WHERE a.table_type = ''base table''
      AND (b.DATA_TYPE = ''text''
      OR b.DATA_TYPE = ''ntext''
      OR b.DATA_TYPE = ''image''
      OR b.DATA_TYPE = ''xml''
      OR (
            (b.DATA_TYPE = ''varchar''
            OR b.DATA_TYPE = ''nvarchar''
            OR b.DATA_TYPE = ''varbinary'')
            AND
            b.CHARACTER_MAXIMUM_LENGTH = -1)
      )'
      EXEC sp_executeSQL @SQL
            
      OPEN TableCursorOnline
      FETCH NEXT FROM TableCursorOnline INTO @TableName
      WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Reindexing '+ @DatabaseName + '.dbo.' + @TableName + ' online'
            set @SQL='ALTER INDEX ALL ON '+ @DatabaseName + '.dbo.' + @TableName+' REBUILD WITH (ONLINE  = ON)'
            EXEC sp_executeSQL @SQL
            --PRINT @SQL
            FETCH NEXT FROM TableCursorOnline INTO @TableName
      END
      CLOSE TableCursorOnline
      DEALLOCATE TableCursorOnline
      
      OPEN TableCursorOffline
      FETCH NEXT FROM TableCursorOffline INTO @TableName
      WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Reindexing '+ @DatabaseName + '.dbo.' + @TableName + ' offline'
            set @SQL='ALTER INDEX ALL ON '+ @DatabaseName + '.dbo.' + @TableName+' REBUILD WITH (ONLINE  = OFF)'
            EXEC sp_executeSQL @SQL
            --PRINT @SQL
            FETCH NEXT FROM TableCursorOffline INTO @TableName
      END
      CLOSE TableCursorOffline
      DEALLOCATE TableCursorOffline
      FETCH NEXT FROM DatabaseCursor INTO @DatabaseName
END
CLOSE DatabaseCursor    
DEALLOCATE DatabaseCursor
END


0

Featured Post

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question