¿Cómo puedo hacer que SQL Server me envíe por correo electrónico los detalles del error cuando falla un trabajo?

Solución:

Algo que podrías hacer es solo un pensamiento, arrojar ideas …

Cree un solo trabajo que verifique periódicamente la tabla de trabajos en msdb para ver si alguno de los trabajos se muestra como fallado, eso se puede hacer con una buena consulta T-SQL. Luego, puede ir a la tabla sysjobsteps y ver si se establece un registro de salida para el trabajo. Haga que un procedimiento almacenado envíe un correo electrónico adjuntando ese archivo. Podrá ver exactamente lo que hizo el trabajo desde el principio hasta el fallo sin tener que tocar el servidor.

Luego, también podría hacer que el script de PowerShell verifique el registro de eventos en busca de errores. Le permite filtrar bastante para obtener exactamente qué tipos de mensajes está buscando. Puede configurarlo como un trabajo del Agente SQL para que se ejecute periódicamente. Luego, en el script de PowerShell, use el cmdlet de correo electrónico para enviar el mensaje si encuentra uno.

Ideas descabelladas aquí, solo algunas en las que pensé.

Tengo experiencia con la idea antes mencionada. Está bien, pero una mejor idea sería hacer algo como dijo Shawn.

Lo que hicimos fue hacer un trabajo que se ejecuta cada 5 minutos y escanea las tablas MSDB sobre fallas en el trabajo. Para cada trabajo que tenía una falla, ejecutamos el SP spDBA_job_notification con su propia ID, por lo que el SP escaneará los pasos del historial de MSDB en busca de errores y los enviará por correo electrónico a todos. De la documentación del SP: “El procedimiento almacenado usa el ID del trabajo para consultar las tablas del agente msdb para el mensaje de error más reciente para ese trabajo”.

Entonces, en lugar de simplemente cambiar cada trabajo, es mejor crear uno que lo haga todo ;-).

Otra idea es configurar todos los trabajos para que escriban en el Visor de eventos de Windows en caso de errores / fallas y leer desde allí con proc extendido xp_ReadErrorLog o una herramienta automática, si ya tiene eso en su red. Por ejemplo, usamos HPOV para verificar cualquier problema del sistema y podríamos configurar una alerta simple para todos los errores del visor de eventos (sin necesidad de ningún trabajo o procedimiento personalizado).

Pruébelo y simplemente conecte sus variables según sea necesario en TSQL. La clave aquí es poner esto como el último paso de cada trabajo de agente SQL individual, pero cada paso de trabajo anterior debe ir al PASO SIGUIENTE, ya sea FALLO o ÉXITO … Funciona para mí muy bien en su mayor parte, pero por favor informe cualquier problema con el que se encuentre. Estamos en SQL Server 2008 R2, por lo que aquí es donde se usa donde lo configuré actualmente.

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>
            ****** 
            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>SQL Instance</th>
    <th>Job Name</th>
    <th>Step</th>
    <th>Message Text</th>
    <th>Job Run Date</th>
    <th>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

---

---

Utiliza Nuestro Buscador