June 15, 2011 at 8:27 am
Hi there,
I'm running the below t-sql, found on microsoft's website, from a job to run on all servers and databases.However, I get the following issues:
1. I only get this particular error with one database and server.
Msg 102, Sev 15, State 1, Line 17 : Incorrect syntax near '('. [SQLSTATE 42000]
Msg 0, Sev 15, State 1, Line 49 : Executed in DBName: ALTER INDEX [IDX_1] ON [dbo].[STAFFING] REORGANIZE [SQLSTATE 01000]
Msg 0, Sev 15, State 1, Line 49 : Executed in DBName: ALTER INDEX [IDX_2] ON [dbo].[STAFFING] REORGANIZE [SQLSTATE 01000]
Msg 0, Sev 15, State 1, Line 49 : Executed in DBName: ALTER INDEX [IDX_3] ON [dbo].[STAFFING] REORGANIZE [SQLSTATE 01000]
2. It does not skip the system databases
3. It doesn't run the indexing on partitions. I did try creating a new case and sub case statement, but did not work either.
Would anyone know if there is a way I can fix the above, especially number 1, without impacting the other servers and databases. It's being run as a master job.
Any help greatly appreciated.
Code being run:
DECLARE @sqlcommand nvarchar(max)
SELECT @sqlcommand = 'IF ''[?]'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN
USE [?]
SET NOCOUNT ON;
DECLARE @objectid int; DECLARE @indexid int;
DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(max);
DECLARE @objectname nvarchar(max); DECLARE @indexname nvarchar(max);
DECLARE @partitionnum bigint; DECLARE @partitions bigint;
DECLARE @frag float; DECLARE @command nvarchar(max);
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 (DB_ID(), NULL, NULL , NULL, ''LIMITED'')
WHERE avg_fragmentation_in_percent > 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;
Set @command=
Case when @frag <= 69
then N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE''
when @frag >= 70.0
then N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD''
when @partitioncount > 1
then @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10))
END
EXEC (@command);
PRINT N''Executed in [?]: '' + @command;
END;
CLOSE partitions;
DEALLOCATE partitions;
DROP TABLE #work_to_do;
End'
EXEC sp_MSforeachdb @sqlcommand
¤ §unshine ¤
June 15, 2011 at 11:13 pm
sunshine-587009 (6/15/2011)
Hi there,I'm running the below t-sql, found on microsoft's website, from a job to run on all servers and databases.However, I get the following issues:
1. I only get this particular error with one database and server.
Msg 102, Sev 15, State 1, Line 17 : Incorrect syntax near '('. [SQLSTATE 42000]
Msg 0, Sev 15, State 1, Line 49 : Executed in DBName: ALTER INDEX [IDX_1] ON [dbo].[STAFFING] REORGANIZE [SQLSTATE 01000]
Msg 0, Sev 15, State 1, Line 49 : Executed in DBName: ALTER INDEX [IDX_2] ON [dbo].[STAFFING] REORGANIZE [SQLSTATE 01000]
Msg 0, Sev 15, State 1, Line 49 : Executed in DBName: ALTER INDEX [IDX_3] ON [dbo].[STAFFING] REORGANIZE [SQLSTATE 01000]
2. It does not skip the system databases
3. It doesn't run the indexing on partitions. I did try creating a new case and sub case statement, but did not work either.
Would anyone know if there is a way I can fix the above, especially number 1, without impacting the other servers and databases. It's being run as a master job.
Any help greatly appreciated.
...FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')
This due to database compitability level 80 (SQL 2000).
Check it.
June 17, 2011 at 7:57 am
Ok, Excellent... thank you! 🙂
1 down... 🙂
¤ §unshine ¤
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply