February 22, 2012 at 6:14 am
Message
Executed as user: XXX\XXX. Online index operation cannot be performed for index 'XXXX' because the index contains column 'DATA' 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. [SQLSTATE 42000] (Error 2725). The step failed.
i got this error when doing an ALTER INDEX REBUILD. i need to all indexes in a very large database and created a script to generate the ALTER INDEX REBUILD statements for all indexes with over 30% fragmentation. how can i identify the tables with LOBs and create ALTER INDEX REORGANIZE for them?
drop table tempdb..temp_frag
create table tempdb..temp_frag(
tabName varchar(200),
indName varchar(200),
fragpct float
)
insert into tempdb..temp_frag
select distinct S.name + '.' + tbl.[name] TableName, ind.[name], mn.avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats (null, null, null, null, null )as mn
inner join sys.tables tbl on tbl.[object_id] = mn.[object_id]
inner join sys.indexes ind on ind.[object_id] = mn.[object_id]
inner join sys.schemas S on tbl.schema_id = S.schema_id
where [database_id] = 5 and mn.avg_fragmentation_in_percent > 30
order by mn.avg_fragmentation_in_percent desc
select distinct 'ALTER INDEX ALL ON [' + SUBSTRING(tabName,1,3) + '].' + SUBSTRING(tabName,5,100) + '] REBUILD WITH (ONLINE = ON);'
,fragpct
from tempdb..temp_frag
order by fragpct desc
February 22, 2012 at 6:36 am
As the error itself says, you have some LOB columns present in some of your tables/indexes. It is documented that you can rebuild the index ONLINE for those indexes which has LOB columns as the index key. Therefore you need to identify those tables/indexes & you need to do the re-indexing for those indexes in OFFLINE mode (i.e. not specifying ONLINE).
February 22, 2012 at 6:37 am
Join on syscolumns, and add a WHERE clause to omit the following xtypes:
(34,35,99,241) --image, text, ntext, or xml '
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 22, 2012 at 6:42 am
As many people will tell you on this site, not to bother with rewriting a process that's already been done so many times before, check out Ola Hallengren's scripts @ http://ola.hallengren.com/
You can also use this script, it doesn't have as many "options" but it works well straight out of the box and will rebuild/reorg online or offline depending on the index and its fragmentation
/* ############################################################################################################### */
--DYNAMICALLY REORG/REBUILD SERVER INDEXES BY FRAGMENTATION LEVEL
/* ############################################################################################################### */
ALTER PROCEDURE [dbo].[dba_ReBuildOrReorgIndexesByFragLevel] (
@DB varchar(50),
@FillFactor varchar(2),
@MinPages int = 50,
@Exec int,
@FragStart int = 10,
@FragEnd int = 25
)
AS
/*
----------------------------------------------------------------------------------------------------------------
Purpose:Written to dynamically perform maintenance on indexes if fragmentation gets too high
Department:DBA
Created For:The Boss
----------------------------------------------------------------------------------------------------------------
NOTES:* Primary concept originally created by Microsoft *
* Error corrected by Pinal Dave (http://www.SQLAuthority.com) *
* Omits indexes where total pages < 100
----------------------------------------------------------------------------------------------------------------
Created On:03/14/2011
Create By:MyDoggieJessie
----------------------------------------------------------------------------------------------------------------
exec dbo.dba_ReBuildOrReorgIndexesByFragLevel 'CORELIBRARY', '92', 2, 0
*/
SET NOCOUNT ON
IF OBJECT_ID ('tempdb.dbo.#fraglist','u') IS NOT NULL
BEGIN
DROP TABLE #fraglist
END
IF OBJECT_ID ('tempdb.dbo.#tables','u') IS NOT NULL
BEGIN
DROP TABLE #tables
END
-- Declare variables
DECLARE @TableNamesysname,@SQLvarchar(1500)
DECLARE @objectidint,@XTypevarchar(3)
DECLARE @indexidint,@fragdecimal
DECLARE @IdxNamesysname,@Recsint
DECLARE @Errvarchar(500),@iint
DECLARE @Versionvarchar(3),@Schemavarchar(3)
DECLARE @Threshold1decimal,@Threshold2decimal
SET @i = 1
SET @Version = UPPER(CONVERT(varchar(3), SERVERPROPERTY('edition')))
/* ######################################### START MAIN PROCEDURE HERE ########################################## */
CREATE TABLE #Tables (
Idx int IDENTITY(1,1),
TName sysname,
TType varchar(3),
SSchema varchar(3)
)
SET @SQL = '
SELECT CAST(TABLE_NAME AS VARCHAR(100)) AS Table_Name, ''ON'' AS Table_Type, TABLE_SCHEMA
FROM ' + RTRIM(@DB) + '.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE''
AND [table_name] NOT IN (
SELECT DISTINCT a.name
FROM ' + RTRIM(@DB) + '.sys.sysobjects AS a WITH(READUNCOMMITTED)
JOIN ' + RTRIM(@DB) + '.sys.syscolumns AS b WITH(READUNCOMMITTED) ON
a.id=b.id
JOIN ' + RTRIM(@DB) + '.sys.syscolumns AS c WITH(READUNCOMMITTED) ON
c.xtype=b.xtype
WHERE b.xType IN (''34'',''35'',''99'',''241''))
AND TABLE_NAME NOT LIKE ''MS%''
UNION
SELECT CAST(TABLE_NAME AS VARCHAR(100)) AS Table_Name, ''OFF'' AS Table_Type, TABLE_SCHEMA
FROM ' + RTRIM(@DB) + '.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE''
AND [table_name] IN (
SELECT DISTINCT a.name
FROM ' + RTRIM(@DB) + '.sys.sysobjects AS a WITH(READUNCOMMITTED)
JOIN ' + RTRIM(@DB) + '.sys.syscolumns AS b WITH(READUNCOMMITTED) ON
a.id=b.id
JOIN ' + RTRIM(@DB) + '.sys.syscolumns AS c WITH(READUNCOMMITTED) ON
c.xtype=b.xtype
WHERE b.xType IN (''34'',''35'',''99'',''241''))
AND TABLE_NAME NOT LIKE ''MS%''
ORDER BY TABLE_NAME;'
INSERT INTO #Tables
EXEC(@SQL)
-- Create the temporary table.
CREATE TABLE #fraglist (
ObjectName sysname,ObjectId int,IndexName sysname,
IndexId int,Lvl int,CountPages int,
CountRows int,MinRecSize int,MaxRecSize int,
AvgRecSize int,ForRecCount int,Extents int,
ExtentSwitches int,AvgFreeBytes int,AvgPageDensity int,
ScanDensity decimal,BestCount int,ActualCount int,
LogicalFrag decimal,ExtentFrag decimal
)
SET @Recs = (SELECT COUNT(1) FROM #Tables)
WHILE @i <= @Recs
BEGIN
SET @TableName = (SELECT TOP 1 TName FROM #Tables WHERE Idx = @i)
SET @Schema = (SELECT TOP 1 SSchema FROM #Tables WHERE Idx = @i)
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @DB + '.' + @Schema + '.[' + @TableName + ']'')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
SET @i = @i + 1
END
/* Add column needed so we can perform ON/OFFLINE statements */
ALTER TABLE #fraglist
ADD idxType varchar(3) NULL
IF RTRIM(@Version) = 'STA'
BEGIN
UPDATE #fraglist
SET idxType = 'OFF' FROM #Tables WHERE ObjectName = TName
END
ELSE
BEGIN
UPDATE #fraglist
SET idxType = TType FROM #Tables WHERE ObjectName = TName
END
/* Remove records not needed for the Cursor */
DELETE FROM #fraglist
WHERE CountPages < @MinPages
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT
ObjectName, ObjectId, IndexId, LogicalFrag, IndexName,
CASE WHEN INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') IS NULL THEN 'OFF'
ELSE idxType END
FROM #fraglist
WHERE
CountPages > @MinPages --LogicalFrag >= @MaxFrag
AND ISNULL(INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth'), 1) > 0
ORDER BY LogicalFrag DESC
-- Open the cursor.
OPEN indexes
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @TableName, @objectid, @indexid, @frag, @IdxName, @XType
SET @i = 0
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@Exec = 0)
BEGIN
SET @i = @i + 1
IF @frag BETWEEN @FragStart AND @FragEnd
BEGIN
PRINT RTRIM(CONVERT(varchar(10),@i))+'. [' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%] '
+ RTRIM(@IdxName) + ' - ALTER INDEX [' + RTRIM(@IdxName) + '] ON '
+ RTRIM(@DB) + '.' + @Schema + '.[' + RTRIM(@TableName) + ']'
+ ' REORGANIZE WITH ( LOB_COMPACTION = ON )'
END
IF @frag > @FragEnd
BEGIN
PRINT RTRIM(CONVERT(varchar(10),@i))+'. [' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%] '
+ RTRIM(@IdxName) + ' - ALTER INDEX [' + RTRIM(@IdxName) + '] ON '
+ RTRIM(@DB) + '.' + @Schema + '.[' + RTRIM(@TableName) + ']'
+ ' REBUILD WITH ( SORT_IN_TEMPDB = OFF, FILLFACTOR = '
+ @FillFactor + ', MAXDOP = 0 , ONLINE = ' + @XType + ' ) '
END
END
ELSE
BEGIN
SET @i = @i + 1
IF @frag BETWEEN @FragStart AND @FragEnd--> Reorganize indexes
BEGIN
SELECT @SQL = 'ALTER INDEX [' + RTRIM(@IdxName) + '] ON '
+ RTRIM(@DB) + '.' + @Schema + '.[' + RTRIM(@TableName) + ']'
+ ' REORGANIZE WITH ( LOB_COMPACTION = ON )'
BEGIN TRY
PRINT RTRIM(CONVERT(varchar(10),@i))+'. [' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%] '
+ RTRIM(@IdxName) + ' - ALTER INDEX [' + RTRIM(@IdxName) + '] ON '
+ RTRIM(@DB) + '.' + @Schema + '.[' + RTRIM(@TableName) + ']'
+ ' REORGANIZE WITH ( LOB_COMPACTION = ON )'
EXEC (@SQL)
END TRY
BEGIN CATCH
PRINT ('ERROR FOUND: ' + ERROR_MESSAGE())
END CATCH
END
IF @frag > @FragEnd--> Rebuild Indexes
BEGIN
SELECT @SQL = 'ALTER INDEX [' + RTRIM(@IdxName) + '] ON '
+ RTRIM(@DB) + '.' + @Schema + '.[' + RTRIM(@TableName) + ']'
+ ' REBUILD WITH ( SORT_IN_TEMPDB = OFF, FILLFACTOR = '
+ @FillFactor + ', MAXDOP = 0 , ONLINE = ' + @XType + ' ) '
BEGIN TRY
PRINT RTRIM(CONVERT(varchar(10),@i))+'. [' + RTRIM(CONVERT(VARCHAR(15),@frag)) + '%] '
+ RTRIM(@IdxName) + ' - ALTER INDEX [' + RTRIM(@IdxName) + '] ON '
+ RTRIM(@DB) + '.' + @Schema + '.[' + RTRIM(@TableName) + ']'
+ ' REBUILD WITH ( SORT_IN_TEMPDB = OFF, FILLFACTOR = '
+ @FillFactor + ', MAXDOP = 0 , ONLINE = ' + @XType + ' ) '
EXEC (@SQL)
END TRY
BEGIN CATCH
PRINT ('ERROR FOUND: ' + ERROR_MESSAGE())
END CATCH
END
END
FETCH NEXT
FROM indexes
INTO @TableName, @objectid, @indexid, @frag, @IdxName, @XType
END
-- Close and deallocate the cursor.
CLOSE indexes
DEALLOCATE indexes
/* ########################################## END MAIN PROCEDURE HERE ########################################### */
/*
exec dbo.dba_ReBuildOrReorgIndexesByFragLevel 'CORELIBRARY', '96',2, 0
*/
-- Delete the temporary table.
DROP TABLE #fraglist
DROP TABLE #Tables
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 22, 2012 at 6:46 am
If you wrap this around the other procedure (calling the other proc from this) it will loop through all your DB's on a given server and perform the maintenance, omitting any DB you specify. This of course assumes that you don't mind setting the fill factor to the same thing for all your indexes...
ALTER PROCEDURE [dbo].[dba_ExecuteServerIndexMaintenance](
@FillFactor varchar(2),
@MinPages varchar(2) = '50',
@Exec varchar(1)
)
AS
/*
----------------------------------------------------------------------------------------------------------------
Purpose:Executes dbo.dba_ReBuildOrReorgIndexesByFragLevel to run against ALL DB's on the
EXECUTING SERVER; excluding master, tempdb, model, msdb, and distribution
Department:DBA
Created For:The Boss
----------------------------------------------------------------------------------------------------------------
NOTES:1)Passes in parameters to the primary stored-procedure
----------------------------------------------------------------------------------------------------------------
Created On:03/15/2011
Create By:MyDoggieJessie
----------------------------------------------------------------------------------------------------------------
Modified On:
Modified By:
Changes:
1.
----------------------------------------------------------------------------------------------------------------
exec dbo.dba_ExecuteServerIndexMaintenance '92', 2, 0
*/
SET NOCOUNT ON
DECLARE @iint
DECLARE @Recsint
DECLARE @Tablesysname
DECLARE @SQLnvarchar(500)
/* ######################################### START MAIN PROCEDURE HERE ########################################## */
/* Create Temp Table to store the results in */
CREATE TABLE #Results (
Idx int IDENTITY(1,1), TName sysname
)
/* Fetch All the DB's on the Server */
INSERT INTO #Results
EXEC sp_MSForEachDB 'Use [?]; SELECT DB_NAME()'
/* Get rid of the ones we don't want to index */
DELETE FROM #Results
WHERE TName IN ('MASTER', 'TEMPDB', 'MODEL', 'MSDB', 'DISTRIBUTION', 'AdventureWorks', 'ReportServer')
/* Loop through the DB's and kick off the magic */
SET @recs = (SELECT COUNT(1) FROM #Results)
WHILE @Recs <> 0
BEGIN
SET @TABLE = (SELECT Top 1 TName FROM #Results )
SET @SQL = ' exec F1Settings.dbo.dba_ReBuildOrReorgIndexesByFragLevel '''
+ @Table + ''', ''' + @FillFactor + ''', ' + @MinPages + ', ' + @Exec
EXEC sp_executesql @SQL
DELETE FROM #Results WHERE TName = @Table
SET @recs = (SELECT COUNT(1) FROM #Results)
END
/* ########################################## END MAIN PROCEDURE HERE ########################################### */
DROP TABLE #Results
/*
exec dbo.dba_ExecuteServerIndexMaintenance '92', 2, 0
*/
SET NOCOUNT OFF
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 22, 2012 at 6:56 am
cool scripts, thanks!
our environment is actually a little more complicated - can't take the system offline (easily) and we run SAN replication for DR purposes, so rebuilding an entire db of indexes would generate a lot of network traffic. the database is over 400GB so not possible to let fly a complete rebuild. i need to generate this list of ALTER INDEX statements, sorting out the REORGS from the REBUILDS, and then balance them in batches based on table size so that i can run a batch in each night or weekends maintenance windows.
February 22, 2012 at 6:59 am
That's why you should be using Ola's (or Michelle Ulfort) scripts as they do not rebuild everything (maint plans do)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 22, 2012 at 7:01 am
I use modified versions of these scripts on DBS with over 800Gb of data...it actually works pretty well and skips the stuff not needing the index maintenance. Set the last parameter to zero to print out all the statements...1 actually executes them
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 22, 2012 at 7:03 am
MyDoggieJessie (2/22/2012)
I use modified versions of these scripts on DBS with over 800Gb of data...it actually works pretty well and skips the stuff not needing the index maintenance. Set the last parameter to zero to print out all the statements...1 actually executes them
excellent, thanks. i just started going through the scripts on a small test server in case i accidentally launched a complete rebuild. 😀
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply