Capturar duas mensagens de erro/jogar na tabela

Você pode aprimorar o tratamento de erros com eventos estendidos para superar a falha TRY...CATCH que está enfrentando. O padrão de design envolve estas etapas:

1. Crie e inicie uma Sessão de Eventos Estendidos: ela irá capturar sqlserver.error_reportedeventos, filtrados principalmente por SPID.
2. Execute uma instrução em um TRYbloco.
3. Dentro (ou depois) do CATCHbloco, leia os dados da sessão XEvents.
4. Use os dados disponíveis para responder ao(s) erro(s) conforme apropriado.
5. Pare e elimine a sessão XEvents.

Aqui está um exemplo que pode ser executado manualmente em um único lote do SSMS. Apenas certifique-se de substituir todas as ocorrências de "2016" no script pelo seu SPID.

CREATE EVENT SESSION [Error Handling Session(2016)]
ON SERVER 
ADD EVENT sqlserver.error_reported
(
    ACTION(
        sqlserver.session_id,
        sqlserver.sql_text
    )
    WHERE [package0].[not_equal_unicode_string]([message],N'''''') 
    AND [severity]>(10) 
    AND [sqlserver].[session_id]=(2016)
) 
ADD TARGET package0.ring_buffer
WITH (
    --ALLOW_SINGLE_EVENT_LOSS
    --NO_EVENT_LOSS
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,

    MAX_MEMORY=4096 KB,
    MAX_DISPATCH_LATENCY=1 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=ON,
    STARTUP_STATE=OFF
);

ALTER EVENT SESSION [Error Handling Session(2016)] ON SERVER STATE=START;

BEGIN TRY
    BACKUP DATABASE master
    TO DISK = 'master.diff.bak'
    WITH DIFFERENTIAL;
END TRY
BEGIN CATCH
    DECLARE @XEData XML

    SELECT @XEData = CAST(xet.target_data AS XML)
    FROM sys.dm_xe_session_targets AS xet
    JOIN sys.dm_xe_sessions AS xe
        ON (xe.address = xet.event_session_address)
    WHERE xe.name = 'Error Handling Session(2016)';

    /*
    Check value of "totalEventsProcessed" to ensure events have been 
    dispatched to event session target (ring_buffer).
    If no events have been processed, delay for a period of MAX_DISPATCH_LATENCY +1 (in seconds).
    */
    IF @XEData.value('(/RingBufferTarget/@totalEventsProcessed)[1]', 'INT') = 0
    BEGIN
        WAITFOR DELAY '00:00:02';

        SELECT @XEData = CAST(xet.target_data AS XML)
        FROM sys.dm_xe_session_targets AS xet
        JOIN sys.dm_xe_sessions AS xe
            ON (xe.address = xet.event_session_address)
        WHERE xe.name = 'Error Handling Session(2016)';
    END

    --Comment/uncomment as desired to show the formatted XML session data.
    SELECT @XEData;

    --Shred the XML. Do whatever you want with this data: 
    -- log it to a table, create and send an email alert, etc.
    SELECT 
        x.c.value(N'(@name)[1]', N'NVARCHAR(MAX)') AS EventName,
        x.c.value(N'(@timestamp)[1]', N'datetime') AS EventTime,
        x.c.value(N'(data[@name="error_number"]/value)[1]', N'NVARCHAR(MAX)') AS ErrorNumber,
        x.c.value(N'(data[@name="severity"]/value)[1]', N'NVARCHAR(MAX)') AS Severity,
        x.c.value(N'(data[@name="state"]/value)[1]', N'NVARCHAR(MAX)') AS [State],
        x.c.value(N'(data[@name="user_defined"]/value)[1]', N'NVARCHAR(MAX)') AS UserDefined,
        x.c.value(N'(data[@name="category"]/text)[1]', N'NVARCHAR(MAX)') AS Category,
        x.c.value(N'(data[@name="destination"]/text)[1]', N'NVARCHAR(MAX)') AS Destination,
        x.c.value(N'(data[@name="is_intercepted"]/value)[1]', N'NVARCHAR(MAX)') AS IsIntercepted,
        x.c.value(N'(data[@name="message"]/value)[1]', N'NVARCHAR(MAX)') AS [Message],
        x.c.value(N'(action[@name="sql_text"]/value)[1]', N'NVARCHAR(MAX)') AS SqlText,
        x.c.value(N'(action[@name="session_id"]/value)[1]', N'NVARCHAR(MAX)') AS SessionId
    FROM @XEData.nodes('//RingBufferTarget/event') AS x(c)
    --WHERE x.c.value(N'(data[@name="destination"]/text)[1]', N'NVARCHAR(MAX)') = 'USER'

END CATCH

ALTER EVENT SESSION [Error Handling Session(2016)] ON SERVER STATE=STOP;
DROP EVENT SESSION [Error Handling Session(2016)] ON SERVER;
GO

Depois de executar o script, a saída do SSMS deve ser semelhante a esta:

Há muitas partes móveis lá. Mas você pode ser capaz de fazê-lo funcionar para você. Eu escrevi algumas postagens de blog relacionadas sobre TRY...CATCH que podem ser úteis:

A promessa não cumprida de TRY...CATCH

Tratamento de erros T-SQL aprimorado com eventos estendidos

Parte 2: Tratamento de erros de T-SQL aprimorado com eventos estendidos

O problema é que há casos em que a instrução de backup retorna mais de um erro, então ERROR_MESSAGE() não é suficiente.

No trabalho do SQL Agent que você usa para fazer backup de seus bancos de dados com dispositivos de backup, apenas faça com que cada etapa passe para a próxima etapa, com falha ou sucesso. No entanto, faça com que a última etapa tenha a lógica abaixo e diga a ela para relatar falha em falha e relatar sucesso em sucesso .

Isso enviará a você por e-mail os detalhes da falha das tabelas msdb para esse trabalho, como você veria na área do histórico de trabalhos do SQL Agent para os registros de data atuais. Você sempre pode verificar o histórico do SQL Agent manualmente quando receber a notificação por e-mail, mas isso funciona bem para a necessidade em que o configurei.

Tenho certeza de que você também pode usar a lógica e fazer algumas alterações para atender às suas necessidades. No meu caso, cada tarefa de backup para o dispositivo de backup é uma etapa separada, cada uma passando para a próxima etapa, independentemente dos resultados, e a última etapa é como indiquei acima e é suficiente para o monitoramento de falhas.

Se eu não estiver no escritório ou por conexão VPN, os dados da tabela HTML enviados por e-mail me darão uma ideia se isso precisa de atenção e assim por diante, verificando o Outlook Web Mail.

WAITFOR DELAY '00:00:10'  ----ten second pause to ensure everything needed is written to table
SELECT  step_name, message
FROM    msdb.dbo.sysjobhistory
WHERE   instance_id > COALESCE((SELECT MAX(instance_id) FROM msdb.dbo.sysjobhistory
                                WHERE job_id = $(ESCAPE_SQUOTE(JOBID)) AND step_id = 0), 0)
        AND job_id = $(ESCAPE_SQUOTE(JOBID))
        AND run_status <> 1 -- success

IF      @@ROWCOUNT <> 0
BEGIN
        RAISERROR('*** SQL Agent Job Prior Step Failure Occurred ***', 16, 1)

DECLARE @job_name NVARCHAR(256) = (SELECT name FROM msdb.dbo.sysjobs WHERE job_id = $(ESCAPE_SQUOTE(JOBID)))
DECLARE @email_profile NVARCHAR(256) = 'SQLInstance Name Alerts'
DECLARE @emailrecipients NVARCHAR(500) = '[email protected]'
DECLARE @subject NVARCHAR(MAX) = 'SQL Server Agent Job Failure Report: ' + @@SERVERNAME
DECLARE @msgbodynontable NVARCHAR(MAX) = 'SQL Server Agent Job Failure Report For: "' + @job_name + '"'

--Dump report data to a temp table to be put into XML formatted HTML table to email out
SELECT sjh.[server]
    ,sj.NAME
    ,sjh.step_id
    ,sjh.[message]
    ,sjh.run_date
    ,sjh.run_time
INTO #TempJobFailRpt
FROM msdb..sysjobhistory sjh
INNER JOIN msdb..sysjobs sj ON (sj.job_id = sjh.job_id)
WHERE run_date = convert(INT, convert(VARCHAR(8), getdate(), 112))
    AND run_status != 4 -- Do not show status of 4 meaning in progress steps
    AND run_status != 1 -- Do not show status of 1 meaning success
    AND NAME = @job_name
ORDER BY run_date

IF EXISTS (
        SELECT *
        FROM #TempJobFailRpt
        )
BEGIN

-----Build report to HTML formatted email using FOR XML PATH
DECLARE @tableHTML NVARCHAR(MAX) = '
<html>
<body>
    <H1>' + @msgbodynontable + '</H1>
        <table border="1" style=
        "background-color: #C0C0C0; border-collapse: collapse">
        <caption style="font-weight: bold">
                ****** Below is the SQL Agent history detail from the SQL Agent job named: ''' + @job_name + ''' . ******
        </caption>

<tr>
    <th style="width:25%; text-decoration: underline">SQL Instance</th>
    <th style="text-decoration: underline">Job Name</th>
    <th style="text-decoration: underline">Step</th>
    <th style="text-decoration: underline">Message Text</th>
    <th style="text-decoration: underline">Job Run Date</th>
    <th style="text-decoration: underline">Job Run Time</th>
</tr>' + CAST((
            SELECT td = [server]
                ,''
                ,td = NAME
                ,''
                ,td = step_id
                ,''
                ,td = [message]
                ,''
                ,td = run_date
                ,''
                ,td = run_time
            FROM #TempJobFailRpt a
            ORDER BY run_date
            FOR XML PATH('tr')
                ,TYPE
                ,ELEMENTS XSINIL -- brings back non isnull NULL column values as empty without making HTML table look odd for empty value otherwise
            ) AS NVARCHAR(MAX)) + '
    </table>
</body>
</html>';

EXEC msdb.dbo.sp_send_dbmail @profile_name = @email_profile
    ,@recipients = @emailrecipients
    ,@subject = @subject
    ,@body = @tableHTML
    ,@body_format = 'HTML'

--Drop Temp table
    DROP TABLE #TempJobFailRpt
END
ELSE
BEGIN
    PRINT '*** No Records Generated ***' 
    DROP TABLE #TempJobFailRpt
END
END

Consulta do DMason com alça spid:

DECLARE @Spid varchar(6)
DECLARE @Sql varchar(max)
SELECT @Spid = @@spid
SET @sql = 
'CREATE EVENT SESSION [Error Handling Session(' + @spid + ')]
ON SERVER 
ADD EVENT sqlserver.error_reported
(
    ACTION(
        sqlserver.session_id,
        sqlserver.sql_text
    )
    WHERE [package0].[not_equal_unicode_string]([message],N'''''''''''') 
    AND [severity]>(10) 
    AND [sqlserver].[session_id]=(' + @spid + ')
) 
ADD TARGET package0.ring_buffer
WITH (
    --ALLOW_SINGLE_EVENT_LOSS
    --NO_EVENT_LOSS
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,

    MAX_MEMORY=4096 KB,
    MAX_DISPATCH_LATENCY=1 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=ON,
    STARTUP_STATE=OFF
);

ALTER EVENT SESSION [Error Handling Session(' + @spid + ')] ON SERVER STATE=START;'
EXEC(@Sql)
BEGIN TRY
    BACKUP DATABASE master
    TO DISK = 'master.diff.bak'
    WITH DIFFERENTIAL;
END TRY
BEGIN CATCH
    DECLARE @XEData XML
    DECLARE @XEName varchar(100) = 'Error Handling Session(' + @spid + ')' 

    SELECT @XEData = CAST(xet.target_data AS XML)
    FROM sys.dm_xe_session_targets AS xet
    JOIN sys.dm_xe_sessions AS xe
        ON (xe.address = xet.event_session_address)
    WHERE xe.name = @XEName;

    /*
    Check value of "totalEventsProcessed" to ensure events have been 
    dispatched to event session target (ring_buffer).
    If no events have been processed, delay for a period of MAX_DISPATCH_LATENCY +1 (in seconds).
    */
    IF @XEData.value('(/RingBufferTarget/@totalEventsProcessed)[1]', 'INT') = 0
    BEGIN
        WAITFOR DELAY '00:00:02';

        SELECT @XEData = CAST(xet.target_data AS XML)
        FROM sys.dm_xe_session_targets AS xet
        JOIN sys.dm_xe_sessions AS xe
            ON (xe.address = xet.event_session_address)
        WHERE xe.name = @XEName;
    END

    --Comment/uncomment as desired to show the formatted XML session data.
    SELECT @XEData;

    --Shred the XML. Do whatever you want with this data: 
    -- log it to a table, create and send an email alert, etc.
    SELECT 
        x.c.value(N'(@name)[1]', N'NVARCHAR(MAX)') AS EventName,
        x.c.value(N'(@timestamp)[1]', N'datetime') AS EventTime,
        x.c.value(N'(data[@name="error_number"]/value)[1]', N'NVARCHAR(MAX)') AS ErrorNumber,
        x.c.value(N'(data[@name="severity"]/value)[1]', N'NVARCHAR(MAX)') AS Severity,
        x.c.value(N'(data[@name="state"]/value)[1]', N'NVARCHAR(MAX)') AS [State],
        x.c.value(N'(data[@name="user_defined"]/value)[1]', N'NVARCHAR(MAX)') AS UserDefined,
        x.c.value(N'(data[@name="category"]/text)[1]', N'NVARCHAR(MAX)') AS Category,
        x.c.value(N'(data[@name="destination"]/text)[1]', N'NVARCHAR(MAX)') AS Destination,
        x.c.value(N'(data[@name="is_intercepted"]/value)[1]', N'NVARCHAR(MAX)') AS IsIntercepted,
        x.c.value(N'(data[@name="message"]/value)[1]', N'NVARCHAR(MAX)') AS [Message],
        x.c.value(N'(action[@name="sql_text"]/value)[1]', N'NVARCHAR(MAX)') AS SqlText,
        x.c.value(N'(action[@name="session_id"]/value)[1]', N'NVARCHAR(MAX)') AS SessionId
    FROM @XEData.nodes('//RingBufferTarget/event') AS x(c)
    --WHERE x.c.value(N'(data[@name="destination"]/text)[1]', N'NVARCHAR(MAX)') = 'USER'

END CATCH
SET @Sql = 
'ALTER EVENT SESSION [Error Handling Session(' + @Spid + ')] ON SERVER STATE=STOP;
DROP EVENT SESSION [Error Handling Session(' + @Spid + ')] ON SERVER;'
EXEC(@sql)
GO