Como posso fazer com que o SQL Server me envie por e-mail os detalhes do erro quando um trabalho falha?

Algo que você pode fazer é apenas um pensamento, lançando ideias...

Crie um único trabalho que verifique periodicamente a tabela de trabalhos no msdb para ver se algum trabalho mostra falha, isso pode ser feito com uma boa consulta T-SQL . Em seguida, você pode acessar a tabela sysjobsteps e ver se um log de saída está definido para o trabalho. Faça com que um procedimento armazenado envie um e-mail anexando esse arquivo a ele. Você seria capaz de ver exatamente o que o trabalho fez desde o início até a falha sem precisar tocar no servidor.

Em seguida, também pode fazer com que o script do PowerShell verifique se há erros no log de eventos. Ele permite que você filtre bastante para obter exatamente os tipos de mensagem que está procurando. Você pode configurar isso como um trabalho do SQL Agent para ser executado periodicamente. Em seguida, no script do PowerShell, use o cmdlet de email para enviar a mensagem, se encontrar uma.

Idéias absurdas aqui, apenas algumas que eu pensei.

Eu tenho experiência com a idéia acima . É bom, mas uma ideia melhor seria fazer algo como Shawn disse.

O que fizemos foi criar um trabalho que é executado a cada 5 minutos e verifica as tabelas MSDB sobre falhas no trabalho. Para cada trabalho com falha, executaríamos o SP spDBA_job_notification com seu próprio ID, para que o SP verificasse as etapas do histórico do MSDB em busca de erros e as enviasse por e-mail. Na documentação do SP: "O procedimento armazenado usa a ID do trabalho para consultar as tabelas do agente msdb para obter a mensagem de erro mais recente desse trabalho."

Portanto, em vez de apenas mudar todos os trabalhos, é melhor criar um único que faça tudo ;-).

Outra ideia é definir todos os trabalhos para gravar no Windows Event Viewer em caso de erros/falhas e ler a partir daí com proc estendido xp_ReadErrorLog ou uma ferramenta automática, se você já tiver isso em sua rede. Por exemplo, usamos HPOV para verificar quaisquer problemas do sistema e podemos configurar um alerta simples para todos os erros do visualizador de eventos (sem necessidade de nenhum trabalho ou procedimento personalizado).

Experimente e apenas conecte suas variáveis ​​conforme necessário no TSQL. A chave aqui é colocar isso como a última etapa de cada trabalho individual do agente SQL, mas cada etapa do trabalho acima precisa ir para a PRÓXIMA ETAPA, seja FALHA ou SUCESSO ... Funciona para mim muito bem na maior parte, mas por favor relate quaisquer problemas que encontrar. Estamos no SQL Server 2008 R2, então é aqui que ele é usado onde o configurei atualmente.

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) = 'SQLServer 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">
            ****** 
            Failure occurred in the SQL Agent job named: ''' + @job_name + ''' in at least one of the steps. 
            Below is the job failure history detail for ALL runs of this job today without needing to connect to SSMS to check.
            ******
        </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
            ) 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

Ainda outra resposta para essa pergunta realmente antiga, esse procedimento armazenado parece funcionar muito bem.

https://karaszi.com/mailafterjob-send-email-after-agent-job-include-ouput-files

Tibor incluiu um ótimo resumo do procedimento armazenado, descrição dos parâmetros e exemplo.