0

Every night at the same time I receive some mails like that: DESCRIPTION: Error: 50000 Severity: 16 State: 1 [ERROR]:DWHDumpProcess_XXX job is either failed or not in good state. Please check. Return Code (null) sent by different DWH SQL servers.

The problem is what there is no job on the server which has a name DWHDumpProcess_XXX. I've checked all mail-related tables in MSDB and can't find a possibility to find a job which sends an information. Do one of you have an idea how I can verify the sender? (accordingly to the email header it's an SQL server in question, not a SMTP-faked email)

Mainecoon21
  • 171
  • 8
  • Check your logs. If the DB is sending out emails, then it probably crying for help and wants you to look at something. – Michael Kutz Nov 29 '21 at 11:12
  • I would check msdb.dbo.sysmail_sentitems to be sure that it was sent from this SQL server.

    select top 100 * from msdb.dbo.sysmail_sentitems where body like '%DWHDumpProcess%'

    Also, it's worth to check if there is such message in sys.messages table.

     – Andrey Samykin Nov 29 '21 at 11:24
  • I could be mistaken, but I don't believe sysmail_sentitems logs Notification Emails on Job failures. Have you actually checked the SQL Server Agent - Jobs to see if there's one with a similar name to the error email you received? Otherwise, as Michael suggests, check your Log file which should essentially be a text file stored in the directory where your SQL instance is installed to. – J.D. Nov 29 '21 at 12:55
  • Error 50000 is not a system message, but a hand-written one. I wouldn't take any of the wording too literally. Could DWHDumpProcess_XXX be a stored procedure or some other concept of "job" that isn't a SQL Agent job? – AMtwo Nov 29 '21 at 12:57
  • @AndreySamykin, I've checked all mail tables and sys.messages: SQL agent sent an email, no one SQL job has a title similar to the mentioned in the email. No custom messages defined. – Mainecoon21 Nov 29 '21 at 13:58
  • Did you check if there is a SQL Agent alert configured to notify an operator by email when error number 50000 occurs? – Eric Prévost Nov 29 '21 at 15:24
  • @EricPrévost, SQL server is configured to notify if severity 16 occures, that's enough (correct me please if I'm wrong) – Mainecoon21 Nov 29 '21 at 15:36
  • @AMtwo, thank you for the point with 50000 as hand-written message, that is something I missed indeed! There is no one database expect SSIS on this server, no additional SPs so I pass this problem to the development to check a code. – Mainecoon21 Nov 29 '21 at 15:36

0 Answers0