bibi92
asked on
Error on procedure for defrag indexes
Hello,
I use this procedure for defrag indexes and it executes successfully on all instances but in the instance which contains subscriptions, I have this error :
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '('.
This is the procedure :
USE [master]
GO
/****** Object: StoredProcedure [dbo].[IndexOptimize] Script Date: 10/24/2010 18:14:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[IndexOptimize]
as
DECLARE @command nvarchar(Max)
SELECT @command = 'USE [?]
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_perce nt AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_s tats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')
WHERE avg_fragmentation_in_perce nt > 10.0 AND index_id > 0;
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
OPEN partitions;
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(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 = QUOTENAME(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;
IF @frag < 30.0
SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE'';
IF @frag >= 30.0
SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD'';
IF @partitioncount > 1
SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N''Executed: '' + @command;
END;
CLOSE partitions;
DEALLOCATE partitions;
DROP TABLE #work_to_do;
'
print @command
EXEC sp_MSforeachdb @command
Thanks
bibi
I use this procedure for defrag indexes and it executes successfully on all instances but in the instance which contains subscriptions, I have this error :
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '('.
This is the procedure :
USE [master]
GO
/****** Object: StoredProcedure [dbo].[IndexOptimize] Script Date: 10/24/2010 18:14:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[IndexOptimize]
as
DECLARE @command nvarchar(Max)
SELECT @command = 'USE [?]
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_perce
INTO #work_to_do
FROM sys.dm_db_index_physical_s
WHERE avg_fragmentation_in_perce
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
OPEN partitions;
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(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 = QUOTENAME(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;
IF @frag < 30.0
SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE'';
IF @frag >= 30.0
SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD'';
IF @partitioncount > 1
SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N''Executed: '' + @command;
END;
CLOSE partitions;
DEALLOCATE partitions;
DROP TABLE #work_to_do;
'
print @command
EXEC sp_MSforeachdb @command
Thanks
bibi
ASKER
Sorry, there is already a quote on this line and this stored proc works on others instances.
Thanks
bibi
Thanks
bibi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot bibi
SELECT @command = 'USE [?]