SQL Server Performance

Index rebuilding job failing as sql job but works when run manually in query analyzer

Discussion in 'ALL SQL SERVER QUESTIONS' started by sandeepsingh_05, Apr 19, 2013.

  1. sandeepsingh_05 New Member

    Friends,

    I have an index rebuilding job (stored proc) that always fails in 2 instances but works fine on all other servers. That too it fails only when running as a sql job, when i run it manually in query analyzer it works fine. Below is the error that is returned everytime. i am uploading the index rebuild script that i am using.

    Date 4/14/2013 1:00:01 AM
    Log Job History (DBA - User DBReindex)
    Step ID 1
    Server KSTLMSQLU30VS4\SQLINS4
    Job Name DBA - User DBReindex
    Step Name S01 - User DBReindex
    Duration 00:00:03
    Sql Severity 16
    Sql Message ID 3621
    Operator Emailed
    Operator Net sent
    Operator Paged
    Retries Attempted 0
    Message
    Executed as user: CHTR\svc_sqlmail. String or binary data would be truncated. [SQLSTATE 22001] (Error 8152) ************************************************************************************************** [SQLSTATE 01000] (Error 0) SQLDBA [SQLSTATE 01000] (Error 0) ************************************************************************************************** [SQLSTATE 01000] (Error 0) ************************************************************************************************** [SQLSTATE 01000] (Error 0) AOP [SQLSTATE 01000] (Error 0) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed

    and here is the index rebuilding code -

    Code:
    USE [SQLDBA]
    GO
    
    /****** Object:  StoredProcedure [dbo].[UspRebuildIndexes]    Script Date: 04/19/2013 05:33:31 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
     
    --sp_helptext UspRebuildIndexes
    --Text
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------modified by sandeep singh (DBA) to increase the size from 130 to 150 of the declared variables of @schemaname ,objectname ,indexname  -----------------------------------------
    --select * from master..sysdatabases
    
    --CREATE
    CREATE PROCedure [dbo].[UspRebuildIndexes]
    AS
    SET NOCOUNT ON;
    DECLARE @objectid int;
    DECLARE @indexid int;
    DECLARE @partitioncount bigint;
    DECLARE @schemaname nvarchar(150);
    DECLARE @objectname nvarchar(200);
    DECLARE @indexname nvarchar(200);
    DECLARE @partitionnum bigint;
    DECLARE @partitions bigint;
    DECLARE @frag float;
    DECLARE @SQL VARCHAR(8000)
    DECLARE @command nvarchar(4000);
    DECLARE @DatabaseID Varchar(15);
    DECLARE @DatabaseName Varchar(150);
    -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
    -- and convert object and index IDs to names.
    IF  EXISTS (SELECT * FROM SQLDBA.sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[work_to_do]') AND type in (N'U'))
    DROP TABLE SQLDBA.[dbo].[work_to_do]
    
    DECLARE Databases CURSOR STATIC FOR SELECT dbid FROM sys.sysdatabases where dbid > 4;
    OPEN Databases
    FETCH Databases into @DatabaseID
      WHILE (@@FETCH_STATUS= 0)
    BEGIN
    PRINT '**************************************************************************************************';
    SELECT @DatabaseName = DB_NAME(@DatabaseID)
    PRINT DB_NAME(@DatabaseID)
    
      SET @SQL = 'Use [' + @DatabaseName + ']' + Char(13) + 'SELECT
      A.object_id AS objectid,
      C.name AS ObjectName,
      S.name AS SchemaName,
      B.name AS IndexName,
      A.index_id AS indexid,
      A.partition_number AS partitionnum,
      avg_fragmentation_in_percent AS frag
      INTO SQLDBA.DBO.work_to_do
      FROM [' + @DatabaseName + '].sys.dm_db_index_physical_stats (' + @DatabaseID +', NULL, NULL , NULL,' + ''''+ 'LIMITED' + '''' +')
      AS A
      JOIN sys.indexes AS B ON A.object_id = B.object_id AND A.index_id = B.index_id
      JOIN sys.objects AS C ON A.object_id = C.object_id AND B.object_id = C.object_id
      JOIN sys.schemas AS S ON C.schema_id = S.schema_id
      WHERE avg_fragmentation_in_percent > 10.0 AND A.index_id > 0;'
    
    --Print @SQL
    EXECUTE(@SQL)
     
    --select @SQL
    --select * from SQLDBA.DBO.work_to_do
    
    -- Declare the cursor for the list of partitions to be processed.
    
    DECLARE partitions CURSOR FOR SELECT objectid, indexid,partitionnum,frag FROM SQLDBA.DBO.work_to_do;
    -- Open the cursor.
    
    OPEN partitions;
    -- Loop through the partitions.
    WHILE (1=1)
        BEGIN;
            FETCH NEXT
              FROM partitions
              INTO @objectid, @indexid, @partitionnum, @frag;
            IF @@FETCH_STATUS < 0 BREAK;
    
      SELECT @objectname = ObjectName,@schemaname= SchemaName
            FROM SQLDBA.DBO.work_to_do AS o
            WHERE o.objectid = @objectid;
    
      SELECT @indexname = IndexName
            FROM SQLDBA.DBO.work_to_do
            WHERE  objectid = @objectid AND indexid = @indexid;
    
    --  SELECT @partitioncount = count (*)
    --        FROM sys.partitions
    --        WHERE object_id = @objectid AND index_id = @indexid;
    --  PRINT @objectid
    --  PRINT @objectname
    --  PRINT @indexname
    -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
    
            SET @command = N'ALTER INDEX [' + @indexname + N'] ON [' + @DatabaseName + N'].['+ @schemaname + N'].[' + @objectname + N'] SET (ALLOW_PAGE_LOCKS = ON)';
            EXEC (@command);
      PRINT N'Executed: ' + @command;
            IF @frag < 30.0
                SET @command = N'ALTER INDEX [' + @indexname + N'] ON [' + @DatabaseName + N'].['+ @schemaname + N'].[' + @objectname + N'] REORGANIZE';
            IF @frag >= 30.0
                SET @command = N'ALTER INDEX [' + @indexname + N'] ON [' + @DatabaseName + N'].['+ @schemaname + N'].[' + @objectname + N'] REORGANIZE';
                --SET @command = N'ALTER INDEX [' + @indexname + N'] ON [' + @DatabaseName + N'].['+ @schemaname + N'].[' + @objectname + N'] REBUILD WITH (ALLOW_PAGE_LOCKS = ON)';
    --        IF @partitioncount > 1
    --            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
            EXEC (@command);
      PRINT N'Executed: ' + @command;
        END;
            PRINT '**************************************************************************************************';
    -- Close and deallocate the cursor.
    CLOSE partitions;
    DEALLOCATE partitions;
    -- Drop the temporary table.
    DROP TABLE SQLDBA.DBO.work_to_do;
    FETCH Databases into @DatabaseID
    END;
    CLOSE Databases;
    DEALLOCATE Databases;
         
    GO
     
    
  2. Shehap MVP, MCTS, MCITP SQL Server

    I do forecast it is related to DB having either :

    · Name size more than the defined DB size of @Databasename variable which is less probable coz I don’t think you have a DB name more than 150 character

    · Or Object name more than defined DB size of @objectname variable

    · Or Index_name more than the define DB size of @indexname variable which is much likelihood

    · Or might be SQL Statement size is more than @sql variable and also it is much probable

    I do recommend to use alter the variable as below and try it again :


    DECLARE @schemaname nvarchar(500);
    DECLARE @objectname nvarchar(500);
    DECLARE @indexname nvarchar(500);
    DECLARE @SQL VARCHAR(max)

    If it is still not coming , please let me know
  3. sandeepsingh_05 New Member

    Hey Shehap,
    Thank you for the prompt reply. I made the changes but the job is still failing with the same error -

    Executed as user: sss\sssss. String or binary data would be truncated. [SQLSTATE 22001] (Error 8152) ************************************************************************************************** [SQLSTATE 01000] (Error 0) SQLDBA [SQLSTATE 01000] (Error 0) ************************************************************************************************** [SQLSTATE 01000] (Error 0) ************************************************************************************************** [SQLSTATE 01000] (Error 0) AOP [SQLSTATE 01000] (Error 0) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
  4. FrankKalis Moderator

    Nothings' wrong with your original variable declaration. Object names in SQL Server all follow the restrictions for Database Identifiers. As far as I can see it, your problem is the USE statement in conjunction with a wrong line break. Try this:
    Code:
      SET @SQL = 'Use [' + @DatabaseName + ']'
      + Char(13)+ Char(10) +
      'SELECT
      A.object_id AS objectid,
      C.name AS ObjectName,
  5. sandeepsingh_05 New Member

    Hey Frank,

    The job failed again with the same error.
  6. FrankKalis Moderator

    Hm, hard to tell, if it is now failing on the same line of code as before. You might be able to test this by wrapping the whole SELECT ...INTO... statement into an SET ANSI_WARNINGS OFF ... SET ANSI_WARNINGS ON, but to be completely honest, rather than reinventing the wheel, I'd probably go for a differerent approach and use readily available scripts such as Ola Hallengren's one
  7. Shehap MVP, MCTS, MCITP SQL Server

    If it is not the case neither variable length nor line break , I suggest to expand the length of @command to be nvarchar (max)because I doubt 4000 character length might not meet the size of command when used for these 2 DBs for any reason either wide object names or wide index names

    Please try it and let me know your feedback
  8. sandeepsingh_05 New Member

    Sorry guys, I was on vacation for a week.
    Shehap, I tried that but no use, same error and the same code works fine when run in query analyzer. there is something wrong in SQL installation/ service pack which handling the "job" differently. I am going to run a Profiler with the job id as the filter and see what i get. any other suggestion?
  9. Charandeep Nayyar New Member

    String or binary data should be truncated

    This means that you have any column/size less than you required.
    Otherwise all things fine
  10. sandeepsingh_05 New Member

    Charandeep, but why is it running fine when i run it in query analyzer manually, it fails only when run in SQL Job.

Share This Page