Alter Table fails because column is being replicated

This is a follow up to my post 7/30/2007
<please review>
I have been unsuccessful in removing this column
do I have to rebuild from scripts then migrate data?
Thanks
Mark J. Soule
Can you confirm that you have removed the constraint, run
sp_removedbreplication (assuming the database isn't used as a publisher or
subscriber) and run sp_MSunmarkreplinfo and still you can't change the table?
Doing all of these should be enough.
Cheers,
Paul Ibison
|||Hi Paul;
Yes I removed all identity range constraints and ran MSunmarkreplinfo.
I gave up and re-created the entire database from scripts then imported
data to the new database.
It was a pain, but it is complete.
thanks
MJ
"Paul Ibison" wrote:

> Can you confirm that you have removed the constraint, run
> sp_removedbreplication (assuming the database isn't used as a publisher or
> subscriber) and run sp_MSunmarkreplinfo and still you can't change the table?
> Doing all of these should be enough.
> Cheers,
> Paul Ibison

Alter Index with REBUILD on master database?

SQL Server 2005:
We plan to use the alter index with rebuild syntax to rebuild our indexes
weekly in a job. Should master and msdb tables be included? I have no
interest in doing it manually a couple times per year if it needs it.
Thanks,
MarkI remember asking the same thing in a SQL 2000 forum a long time ago and the
consensus was you never need to include any of the system databases in
reindexing / update stats. I would gather the same is applicable to SQL
2005.
HTH,
Rubens
"Mark" <mark@.idonotlikespam.com> wrote in message
news:e0zii6#oIHA.420@.TK2MSFTNGP02.phx.gbl...
> SQL Server 2005:
> We plan to use the alter index with rebuild syntax to rebuild our indexes
> weekly in a job. Should master and msdb tables be included? I have no
> interest in doing it manually a couple times per year if it needs it.
> Thanks,
> Mark
>|||Why not?
"Rubens" <rubensrose@.hotmail.com> wrote in message
news:uUmDCKBpIHA.4912@.TK2MSFTNGP03.phx.gbl...
>I remember asking the same thing in a SQL 2000 forum a long time ago and
>the consensus was you never need to include any of the system databases in
>reindexing / update stats. I would gather the same is applicable to SQL
>2005.
> HTH,
> Rubens
> "Mark" <mark@.idonotlikespam.com> wrote in message
> news:e0zii6#oIHA.420@.TK2MSFTNGP02.phx.gbl...
>> SQL Server 2005:
>> We plan to use the alter index with rebuild syntax to rebuild our indexes
>> weekly in a job. Should master and msdb tables be included? I have no
>> interest in doing it manually a couple times per year if it needs it.
>> Thanks,
>> Mark|||Because in SQL2005 there shouldn't be any tables (of consequence) in the
master database that you can actually run UPDATE STATISTICS or rebuild
indexes.
Linchi
"Mark" wrote:
> Why not?
> "Rubens" <rubensrose@.hotmail.com> wrote in message
> news:uUmDCKBpIHA.4912@.TK2MSFTNGP03.phx.gbl...
> >I remember asking the same thing in a SQL 2000 forum a long time ago and
> >the consensus was you never need to include any of the system databases in
> >reindexing / update stats. I would gather the same is applicable to SQL
> >2005.
> >
> > HTH,
> > Rubens
> >
> > "Mark" <mark@.idonotlikespam.com> wrote in message
> > news:e0zii6#oIHA.420@.TK2MSFTNGP02.phx.gbl...
> >> SQL Server 2005:
> >>
> >> We plan to use the alter index with rebuild syntax to rebuild our indexes
> >> weekly in a job. Should master and msdb tables be included? I have no
> >> interest in doing it manually a couple times per year if it needs it.
> >>
> >> Thanks,
> >> Mark
> >>
>
>

Alter Index issue & Try/Catch question

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_NORECOMPUTE=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_NORECOMPUTE=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;
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.googlegr oups.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_NORECOMPUTE=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_NORECOMPUTE=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;
>

Alter Index issue & Try/Catch question

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_NORECOMPUTE=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_NORECOMPUTE=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;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_NORECOMPUTE=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_NORECOMPUTE=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;
>

Alter Index issue & Try/Catch question

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_NORECOMPUTE=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_NORECOMPUTE=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;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_NORECOMPUTE=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_NORECOMPUTE=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;
>