MS SQL Server - Alter Index issue & Try/Catch question

Author

Alter Index issue & Try/Catch question

theredmiata@hotmail.com

2006-12-04, 7:12 pm

All,

I'm currently modifying a BOL procedure to rebuild/reorganize my
indexes. I've changed the ALTER INDEX command so that it performs the
rebuild online. The first time I ran the script, it errored with the
following error below:

Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index
'Company$Attachment$
0' because the index contains column 'Entry Pointer
ID' 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.

At this point, I have tried to integrate the TRY/CATCH routine so that
when this error appears, the script executes the ALTER INDEXES offline
instead (script below). I was wondering if there is a way to possible
write out the error to a log file perhaps?

Thanks,

Ian

SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
-- conditionally select from the function, converting object and index
IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number as partitionnum,
avg_fragmentation_in
_percent as frag
INTO work_to_do
FROM sys. dm_db_index_physical
_stats (5, NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in
_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN;
SELECT @objectname = o.name, @schemaname = s.name
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;

SELECT @indexname = name
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between
reorganizing and rebuilding
IF @frag < 30.0
BEGIN;
SELECT @command = 'ALTER INDEX [' + @indexname + '] ON ' + '[' +
@objectname + '] REORGANIZE';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR,
@partitionnum);
PRINT (@command);
EXEC (@command);
END;

IF @frag >= 30.0
BEGIN;
SELECT @command = 'ALTER INDEX [' + @indexname +'] ON ' + '[' +
@objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPU
TE=OFF) ';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR,
@partitionnum);
BEGIN TRY
EXEC (@command);
END TRY
BEGIN CATCH
SELECT @command = 'ALTER INDEX [' + @indexname +'] ON ' + '[' +
@objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPU
TE=OFF) ';
EXEC (@command);
END CATCH
PRINT (@command);
END;
PRINT 'Executed ' + @command;
PRINT
'------------------------------------------------------------------------------';

FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum,
@frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

Arnie Rowland

2006-12-04, 7:12 pm

To write a event log entry use: RAISERROR

RAISERROR ('Something happened and needs to be logged', 10, 1 ) WITH LOG

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf

<theredmiata@hotmail.com> wrote in message
news:1165272932.638685.169330@n67g2000cwd.googlegroups.com...
> All,
>
> I'm currently modifying a BOL procedure to rebuild/reorganize my
> indexes. I've changed the ALTER INDEX command so that it performs the
> rebuild online. The first time I ran the script, it errored with the
> following error below:
>
> Msg 2725, Level 16, State 2, Line 1
> Online index operation cannot be performed for index
> 'Company$Attachment$
0' because the index contains column 'Entry Pointer
> ID' 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.
>
> At this point, I have tried to integrate the TRY/CATCH routine so that
> when this error appears, the script executes the ALTER INDEXES offline
> instead (script below). I was wondering if there is a way to possible
> write out the error to a log file perhaps?
>
> Thanks,
>
> Ian
>
>
> SET NOCOUNT ON;
> DECLARE @objectid int;
> DECLARE @indexid int;
> DECLARE @partitioncount bigint;
> DECLARE @schemaname sysname;
> DECLARE @objectname sysname;
> DECLARE @indexname sysname;
> DECLARE @partitionnum bigint;
> DECLARE @partitions bigint;
> DECLARE @frag float;
> DECLARE @command varchar(8000);
> -- ensure the temporary table does not exist
> IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
> DROP TABLE work_to_do;
> -- conditionally select from the function, converting object and index
> IDs to names.
> SELECT
> object_id AS objectid,
> index_id AS indexid,
> partition_number as partitionnum,
> avg_fragmentation_in
_percent as frag
> INTO work_to_do
> FROM sys. dm_db_index_physical
_stats (5, NULL, NULL , NULL, 'LIMITED')
> WHERE avg_fragmentation_in
_percent > 10.0 AND index_id > 0;
> -- Declare the cursor for the list of partitions to be processed.
> DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
>
> -- Open the cursor.
> OPEN partitions;
>
> -- Loop through the partitions.
> FETCH NEXT
> FROM partitions
> INTO @objectid, @indexid, @partitionnum, @frag;
>
> WHILE @@FETCH_STATUS = 0
> BEGIN;
> SELECT @objectname = o.name, @schemaname = s.name
> FROM sys.objects AS o
> JOIN sys.schemas as s ON s.schema_id = o.schema_id
> WHERE o.object_id = @objectid;
>
> SELECT @indexname = name
> FROM sys.indexes
> WHERE object_id = @objectid AND index_id = @indexid;
>
> SELECT @partitioncount = count (*)
> FROM sys.partitions
> WHERE object_id = @objectid AND index_id = @indexid;
>
> -- 30 is an arbitrary decision point at which to switch between
> reorganizing and rebuilding
> IF @frag < 30.0
> BEGIN;
> SELECT @command = 'ALTER INDEX [' + @indexname + '] ON ' + '[' +
> @objectname + '] REORGANIZE';
> IF @partitioncount > 1
> SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR,
> @partitionnum);
> PRINT (@command);
> EXEC (@command);
> END;
>
> IF @frag >= 30.0
> BEGIN;
> SELECT @command = 'ALTER INDEX [' + @indexname +'] ON ' + '[' +
> @objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPU
TE=OFF) ';
> IF @partitioncount > 1
> SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR,
> @partitionnum);
> BEGIN TRY
> EXEC (@command);
> END TRY
> BEGIN CATCH
> SELECT @command = 'ALTER INDEX [' + @indexname +'] ON ' + '[' +
> @objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPU
TE=OFF) ';
> EXEC (@command);
> END CATCH
> PRINT (@command);
> END;
> PRINT 'Executed ' + @command;
> PRINT
> '------------------------------------------------------------------------------';
>
> FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum,
> @frag;
> END;
> -- Close and deallocate the cursor.
> CLOSE partitions;
> DEALLOCATE partitions;
>

Sponsored Links