Jak vám dnes můžeme pomoci?

Finding SQL Agent Job Failures

Issue

SQL Agent jobs have the ability to notify a user should a failure occur when a job executes. Many times a job may have multiple sequential independent steps, where execution continues to the next step even after one step fails. When the steps within a job are configured this way, the job does not consider itself to have failed, and does not send a notification.

Resolution

The following query provides information about failed jobs and the failing steps. This query can be modified to filter the results further, query based on a time period, or however necessary to suit its particular application. This query could also be used to send Email alerts to a administrator or group of users, build a report, place the contents into another table, or any other useful purpose. For example, a daily scheduled job could be created to check for failures and alert a administrator via email that it requires attention.


SELECT j.name AS jobName
,h.step_id
,h.step_name
,s.command
,s.database_name
,h.sql_message_id as ErrorID
,h.sql_severity as Severity
,ISNULL(msg.description, '') as Description
,h.message
,h.run_date as runDate
,h.run_time as runTime
,h.server
FROM MSDB.dbo.sysjobhistory h WITH (NOLOCK)
JOIN msdb.dbo.sysJobs j WITH (NOLOCK) ON h.job_id = j.job_id
JOIN msdb.dbo.sysjobsteps s WITH (NOLOCK) ON h.step_id = s.step_id
AND h.job_id = s.job_id
LEFT JOIN master.sys.sysmessages msg WITH (NOLOCK)ON h.sql_message_id = msg.error
AND h.sql_severity = msg.severity
AND msg.msglangid = 1033
WHERE sql_severity > 0

The stored procedure sp_send_dbmail could be used to send the raw query results via e-mail message to the specified recipients. Alternatively, additional scripting could be employed to format the body of the email prior to sending.

See RESL203846 for additional information on Configuring SQL Server to Send Emails.

Schneider Electric Česká republika

  • Dokumentace k produktům
  • Software ke stažení
  • Nástroj pro výběr produktů
  • Hledání náhrady za produkty
  • Centrum nápovědy a kontakty
  • Najít naše pobočky
  • Získejte nabídku
  • Kde koupit
  • Komunita Schneider Electric
  • Kariéra
  • Profil společnosti
  • Nahlásit nevhodné chování
  • Přístupnost
  • Newsroom
  • Investoři
  • EcoStruxure
  • Vyhledávání pracovních míst
  • Blog
  • Všeobecné obchodní podmínky
  • Zásady ochrany osobních údajů
  • Oznámení o souborech cookie
  • Podmínky použití
  • Změnit nastavení souborů cookie