Issues with maintenance plan – SQL 2016

Posted on

Question :

One of my client is having issues with one of the jobs failing every weekend to perform index maintenance on a couple of databases. It is a SQL job which uses maintenance plans underneath. Okay, so the maintenance plans consist of check database integrity task, followed by 3 t-sql task in sequence which runs the same script but for different databases (code provided below) and finally runs a reorg task and then update of statistics. I know this seems a bit weird the way they are running things at this time but I will change it moving forward. Presently, I am stuck with this job completing the maintenance check but failing to execute the t-sql task with the following error.

Executing the query “USE XMain;

declare @frag_Temp as Table
(
…” failed with the following error: “Incorrect syntax near ‘-‘.
Changed database context to ‘XMain’.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

USE XMain;

DECLARE @frag_Temp AS TABLE (
  ID int IDENTITY (1, 1),
  [objectid] [int] NULL,
  [indexid] [int] NULL,
  [partitionnum] [int] NULL,
  [frag] [float] NULL
)

DECLARE @Count int
DECLARE @i tinyint = 1
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitionnum bigint;
DECLARE @partitioncount bigint;
DECLARE @SQLCommand AS nvarchar(3000)
INSERT INTO @frag_Temp
  SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
  FROM sys.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(), NULL, NULL, NULL, 'LIMITED')
  WHERE avg_fragmentation_in_percent >= 30.0
  AND index_id > 0;

-- Get Total Count
SELECT
  @Count = COUNT(*)
FROM @frag_Temp
WHILE (@i <= @Count)
BEGIN
  SELECT
    @objectid = objectid,
    @indexid = indexid,
    @partitionnum = partitionnum
  FROM @frag_Temp
  WHERE ID = @i

  -- Get tableName and its schema
  SELECT
    @objectname = o.name,
    @schemaname = c.name
  FROM sys.objects o
  INNER JOIN sys.schemas c
    ON o.schema_ID = c.schema_ID
  WHERE o.object_id = @objectid
  -- Get Index Name
  SELECT
    @indexname = name
  FROM sys.indexes
  WHERE index_id = @indexid
  AND object_id = @objectid
  -- Get Partition Count
  SELECT
    @partitioncount = COUNT(*)
  FROM sys.partitions
  WHERE object_id = @objectid
  AND index_id = @indexid

  SELECT
    @SQLCommand = 'Alter Index ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REBUILD;'
  IF (@partitioncount > 1)
    SELECT
      @SQLCommand = @SQLCommand + ' PARTITION=' + CONVERT(char, @partitionnum);

  EXEC (@SQLCommand);

  -- Increment Count
  SET @i = @i + 1

END
GO

Answer :

Your client should use the QUOTENAME function, if not, here is an example:

DECLARE @schemaname sysname = 'dbo';
DECLARE @objectname sysname='-mySuperTableName';
DECLARE @indexname sysname = 'ix1';
DECLARE @SQLCommand AS nvarchar(3000)

  SELECT
    @SQLCommand = 'Alter Index ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REBUILD;'
    print  @SQLCommand 

Try to execute this code and the command you received is Alter Index ix1 ON dbo.-mySuperTableName REBUILD; and it definitely gives you an error

Incorrect syntax near ‘-‘.

if you try to execute the command

Leave a Reply

Your email address will not be published.