October 21, 2008 at 7:08 pm
The article about 2005 Online Index Rebuild using DMV seems to be very helpful but when we do online and follow B.system_type_id IN (34,35,99,241) it gave error for varchar(max) and nvarchar(max). but what i did some change B.system_type_id IN (34,35,99,241)or (B.MAX_LENGTH<0 and B.system_type_id IN(167,165,231)). when we run the script we can rebuild it while datebase is online? i also see there is table to collect the data for offline.
The Article i read out from here is pasted below.
CREATE PROCEDURE dbo.usp_OnlineIndexRebuild
@Emailrecipients varchar(MAX) = 'tbollhofer2@gmail.com',
@EmailSubject varchar(MAX) = 'Index Maintenance Report'
AS
DECLARE @dbId INT
SET @dbId = db_id()
CREATE TABLE #fragReport
(
RowID int identity (1,1),
Object_Id int,
Index_Id int,
Index_Name sysname,
LogicalFragmentation float
)
CREATE TABLE #reindex_online
(
RowID int identity (1,1),
Object_Name sysname,
Index_Name sysname
)
CREATE TABLE #reindex_offline
(
RowID int identity (1,1),
Object_Name sysname,
Index_Name sysname
)
CREATE TABLE #contains_text
(
RowID int identity (1,1),
Index_Name sysname
)
INSERT INTO #fragReport([Object_Id], [Index_Id],[Index_Name],[LogicalFragmentation])
SELECT A.object_id,
A.index_id,
[name],
Avg_Fragmentation_In_Percent
FROM sys.dm_db_index_physical_stats (@dbId, NULL,NULL, NULL, NULL) AS A
JOIN sys.indexes B WITH(NOLOCK)
ON A.Object_id = B.Object_id
AND A.Index_id = B.Index_id
WHERE Avg_Fragmentation_In_Percent >= 30
AND B.[name] IS NOT NULL
INSERT INTO #contains_text
SELECT A.Index_Name
FROM #fragReport A
JOIN sys.columns B WITH(NOLOCK)
ON A.Object_Id = B.Object_id
AND B.system_type_id IN (34,35,99,241)
GROUP BY A.Index_Name
INSERT INTO #reindex_online([Object_Name],[Index_Name])
SELECT C.name + '.' + B.name AS [Object_Name],
A.[Index_Name]
FROM #fragReport A WITH(NOLOCK)
JOIN sys.tables B WITH(NOLOCK)
ON A.Object_Id = B.Object_Id
JOIN sys.schemas C WITH(NOLOCK)
ON B.schema_id = C.schema_id
WHERE A.Index_Name NOT IN (SELECT Index_Name
FROM #contains_text WITH(NOLOCK))
INSERT INTO #reindex_offline([Object_Name],[Index_Name])
SELECT C.name + '.' + B.name AS [Object_Name],
A.[Index_Name]
FROM #fragReport A WITH(NOLOCK)
JOIN sys.tables B WITH(NOLOCK)
ON A.Object_Id = B.Object_Id
JOIN sys.schemas C WITH(NOLOCK)
ON B.schema_id = C.schema_id
WHERE A.Index_Name IN (SELECT Index_Name
FROM #contains_text WITH(NOLOCK))
DECLARE @numtables int,
@numindexes int,
@numreindexes int,
@tabcount int,
@indcount int,
@recount int,
@currtable int,
@tabname varchar(255),
@currind int,
@indname varchar(255)
SELECT @numreindexes = count(*) FROM #reindex_online WITH(NOLOCK)
SET @recount = 1
WHILE @recount <= @numreindexes
BEGIN
SELECT @tabname = [Object_Name],
@indname = [Index_Name]
FROM #reindex_online
WHERE RowId = @recount
EXEC ('ALTER INDEX ' + @indname + ' ON ' + @tabname + ' REBUILD WITH(PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON)')
SET @recount = @recount + 1
END
SELECT @numreindexes = count(*) FROM #reindex_offline WITH(NOLOCK)
SET @recount = 1
WHILE @recount <= @numreindexes
BEGIN
SELECT @tabname = [Object_Name],
@indname = [Index_Name]
FROM #reindex_offline
WHERE RowId = @recount
EXEC ('ALTER INDEX ' + @indname + ' ON ' + @tabname + ' REBUILD WITH(PAD_INDEX = OFF, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF)')
SET @recount = @recount + 1
END
DECLARE @tableHTML nvarchar(MAX) ;
SET @tableHTML =
N' ' +
N'
' +
N' ' +
N' ' +
CAST ( ( SELECT td = C.name + '.' + B.name, '',
td = A.Index_Name, '',
td = convert(char(2),convert(int,A.LogicalFragmentation)) + '%', ''
FROM #fragReport A
JOIN sys.tables B WITH(NOLOCK)
ON A.Object_Id = B.Object_Id
JOIN sys.schemas C WITH(NOLOCK)
ON B.schema_id = C.schema_id
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N' ' ;
EXEC msdb.dbo.sp_send_dbmail @recipients = @Emailrecipients,
@subject = @EmailSubject,
@body = @tableHTML,
@body_format = 'HTML' ;
DROP TABLE #fragreport
DROP TABLE #contains_text
DROP TABLE #reindex_online
DROP TABLE #reindex_offline
go
SET ANSI_NULLS OFF
go
SET QUOTED_IDENTIFIER OFF
go
IF OBJECT_ID('dbo.usp_OnlineIndexRebuild') IS NOT NULL
PRINT ' '
ELSE
PRINT ' '
go
October 22, 2008 at 6:08 pm
I am not sure what your question is ?
You cannot rebuild index ONLINE if the table has columns with large datatypes like : image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml. So basically you can exclude these tables by filtering these system_datatypes exclusively.
October 22, 2008 at 7:21 pm
thanks for the reply...
so basically if i need to schedule this task during online through job i can exclude those table by filtering data type..so can u correct me if i am doing right about filtering varchar(max), i included B.system_type_id IN (34,35,99,241)or (B.MAX_LENGTH<0 and B.system_type_id IN(167,165,231)). if not what is the way. script on the article did not filter varchar(max) and nchar(max)
thanks
sagar
October 23, 2008 at 8:34 am
any idea on this process if any body has some suggestion that would be great.
thanks
sagar
October 23, 2008 at 8:53 pm
Hi,
I am getting error while executing the online index rebuild
error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'User'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'User'.
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
is there any idea what might happen. what i suppose to look for i already exclude xml data type
any help would be appreciate.
Thanks
Sagar
October 24, 2008 at 4:02 am
That a a syntax error from the dynamic SQL. Print the SQL before you exec it and you should be able to see exactly what gave the error and what's wrong with it.
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
October 24, 2008 at 4:26 pm
thank you very much that work for me.
sagar
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply