SQL Agent Job Intermittent failure for message 22050

• scarr030

  SSCommitted

  Points: 1699

  More actions

  October 15, 2018 at 8:39 am

  #381163

  Hi all,

  We have a job step that was written years ago and has been running fine for that whole period. The job is to check for any blocking activity and send an email if blocks are found (see code at end of post). This runs every two minutes. All has been good until recently. The step will fail intermittently with SQL Message 22050: Error formatting query, probably invalid parameters. I've done what seems like quite a lot of searching for solutions and have found none. I have changed the step to run in msdb and have prefixed table names with the correct DB name. As far as I can tell, this is running with the appropriate permissions. What's making this particularly difficult to pinpoint is that it's very intermittent. The vast majority of the time the step will run, but occasionally fail due to that. All of the research I have done has shown solutions for steps that just fail every time. Nothing seems to be working. Any suggestions?

  DECLARE @Table TABLE(
   SPID VARCHAR(10),
   Status VARCHAR(MAX),
   LOGIN VARCHAR(MAX),
   HostName VARCHAR(MAX),
   BlkBy VARCHAR(MAX),
   DBName VARCHAR(MAX),
   Command VARCHAR(MAX),
   CPUTime INT,
   DiskIO INT,
   LastBatch VARCHAR(MAX),
   ProgramName VARCHAR(MAX),
   SPID_1 VARCHAR(10),
   REQUESTID INT
)

  INSERT INTO @Table EXEC sp_who2

  if exists(
    SELECT a.SPID, a.BlkBy BlkBy, COUNT(*) ct
    FROM  @Table a
    join @Table b on b.BlkBy != ' .' and b.BlkBy = a.SPID
    group by a.SPID, a.BlkBy
)
begin
    -- if we have a positive (something blocking) wait 5 seconds,
    -- do a second sample, most blocks are really short
    waitfor delay '00:00:05'
    delete from @Table
    INSERT INTO @Table EXEC sp_who2

      if exists(
        SELECT a.SPID, a.BlkBy BlkBy, COUNT(*) ct
        FROM  @Table a
        join @Table b on b.BlkBy != ' .' and b.BlkBy = a.SPID
        group by a.SPID, a.BlkBy
    )
    begin

      
        DELETE FROM [EMR_MONITORING]..BLOCKING_INFO
        WHERE OCCURRED_ON <= DATEADD(MONTH,-1,GETDATE())
        
        INSERT INTO [EMR_MONITORING]..BLOCKING_INFO(OCCURRED_ON,TIME_ELAPSED,SPID,DB,STATUS,SQL)
select GETDATE(),convert(varchar(20),[dd hh:mm:ss.mss]) elapsed, session_id, database_name, status, cast(sql_text as varchar(max))
                from [EMR_MONITORING]..WhoIsActive_OneRun
        where CAST(session_id AS VARCHAR) in
        (    
            SELECT    a.SPID
            FROM    @Table a
            where    a.spid in (
                select b.BlkBy
                from @Table b
                where b.BlkBy <> ' .' and ISNUMERIC(b.BlkBy)=1)
        )
                

          exec msdb..sp_send_dbmail @profile_name = '<PROFILE NAME>',
            @recipients = '<EMAIL ADDRESS>',
            @subject = 'Blocking process on 183?' ,
            @query = '
                DECLARE @Table TABLE(
                    SPID INT,
                    Status VARCHAR(MAX),
                    LOGIN VARCHAR(MAX),
                    HostName VARCHAR(MAX),
                    BlkBy VARCHAR(MAX),
                    DBName VARCHAR(MAX),
                    Command VARCHAR(MAX),
                    CPUTime INT,
                    DiskIO INT,
                    LastBatch VARCHAR(MAX),
                    ProgramName VARCHAR(MAX),
                    SPID_1 INT,
                    REQUESTID INT
                )

                  INSERT INTO @Table EXEC sp_who2
                
                SELECT a.SPID, convert(varchar(10),a.BlkBy) BlkBy, COUNT(*) ct
                FROM  @Table a
                join @Table b on b.BlkBy != '' .'' and b.BlkBy = a.SPID
                group by a.SPID, a.BlkBy

                  delete from [EMR_MONITORING]..WhoIsActive_OneRun
                exec sp_whoisactive @get_transaction_info = 1, @get_plans = 1, @DESTINATION_TABLE = ''WhoIsActive_OneRun'';
                select convert(varchar(20),[dd hh:mm:ss.mss]) elapsed, session_id, database_name, status, cast(sql_text as varchar(max))
                from [EMR_MONITORING]..WhoIsActive_OneRun
        where session_id in
        (    
            SELECT    a.SPID
            FROM    @Table a
            where    a.spid in (
                select CONVERT(int, b.BlkBy)
                from @Table b
                where b.BlkBy != '' .'' and ISNUMERIC(b.BlkBy)=1)
        )',
            @execute_query_database = 'EMR_MONITORING'

          declare @kill_command nvarchar(2000)
        select @kill_command = 'kill ' + CONVERT(varchar,session_id)
        from EMR_MONITORING..WhoIsActive_OneRun
        where session_id in
        (    
            SELECT    a.SPID
            FROM    @Table a
            where    a.BlkBy = ' .'
            and a.spid in (
                select CONVERT(int, b.BlkBy)
                from @Table b
                where b.BlkBy != ' .' and ISNUMERIC(b.BlkBy)=1)
        )
        and
        [dd hh:mm:ss.mss] > '00 00:05:00.000'
        exec sp_executesql @kill_command
    end
end