Online Reindexing MSSQL 2005

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.
LVL 1
skacoreAsked:
Who is Participating?
 
DireOrbAntConnect With a Mentor Commented:
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
 
DireOrbAntCommented:
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
 
skacoreAuthor Commented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
DireOrbAntCommented:
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
 
skacoreAuthor Commented:
You know, that's a good point... :)
0
 
skacoreAuthor Commented:
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
 
skacoreAuthor Commented:
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
 
DireOrbAntCommented:
Odd, is EmailToLong a field on that table?
0
 
skacoreAuthor Commented:
Yes it is.  It is a field of datatype 'text' as well.  Odd indeed!
0
 
skacoreAuthor Commented:
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
 
skacoreAuthor Commented:
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
 
skacoreAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.