Solved

Online Reindexing MSSQL 2005

Posted on 2006-11-05
12
1,770 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
  • 8
  • 4
12 Comments
 
LVL 26

Expert Comment

by:DireOrbAnt
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

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

Author Comment

by:skacore
Comment Utility
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 500 total points
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Author Comment

by:skacore
Comment Utility
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
Comment Utility
Odd, is EmailToLong a field on that table?
0
 
LVL 1

Author Comment

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

Author Comment

by:skacore
Comment Utility
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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now