none
Looking for help on my company DayEnd process and alerting RRS feed

  • Question

  • Hello and good morning to you all.

    In my company I am tasked with writing a SQL query to perform a few actions for our DayEnd processing. First off a little bit of information.

    Our DayEnd does not run every day skipping Saturday and Sunday. It always starts during the night after business hours  so our database is ready for the next business day. At the end of the process it forwards the date ahead by one day. Monday night starts at 7 PM, but Tuesday/Wednesday/Thursday/Friday starting at 8 PM. Monday night starts a bit earlier as it is processing for Saturday and Sunday and can take a bit longer. Also Friday night into Saturday is different than the rest as it processes more than any other day and forwards the business day to Monday when DayEnd will be run again.

    Here are the things I am looking to perform:

    1. Automate the script. DayEnd process starts at 7 PM on Monday; 8 PM Tuesday, Wednesday, Thursday, Friday.

    2. Log the progress of DayEnd. Create a database averaging the times that each Task will take.

    3. If any DayEnd Task falls out of the expectations of when each task will finish send an email. Sometimes Tasks stall out and the process freezes on a Task without us finding out until hours later.

    So those are basic goals right now and a good start. So! Here is the code I was given.

    SELECT  CONVERT(DATE,DLOG.BusinessDate) "Business Date"
           ,DPRTYPE.DayEndProcessTypeName
           ,DLOG.StartDtTm
           ,DLOG.FinishDtTm
           ,(DATEDIFF(second,DLOG.StartDtTm,DLOG.FinishDtTm)*1.00)/60 "Minutes"
           
    
      FROM DATABASENAME.dbo.DayEndLog DLOG
    
          INNER JOIN DATABASENAME.dbo.DayEndProcessType DPRTYPE
                  ON DLOG.DayEndProcessTypeId = DPRTYPE.DayEndProcessTypeId,
            
          (SELECT '04/07/2054' "DDATE") AS "QDATE"
    
    WHERE (CONVERT(DATE,DLOG.BusinessDate) BETWEEN QDATE.DDATE AND DATEADD(day,+2,QDATE.DDATE) AND NOT DLOG.DayEndProcessTypeId IN (90,100,110)) OR (CONVERT(DATE,DLOG.BusinessDate) BETWEEN DATEADD(day,+1,QDATE.DDATE) AND DATEADD(day,+3,QDATE.DDATE) AND DLOG.DayEndProcessTypeId IN (90,100,110))
    
     ORDER BY DLOG.StartDtTm, DLOG.FinishDtTm ASC

    And here are the results. Sorry if the formatting is off.

    Note: the Business Date is in the future as I'm running this on a test database server. Whenever DayEnd is run it pushes the business day ahead by one so that is why the year is 2054. However the Start and Finish days are correct.

    Also I changed the process names to be ambiguous, sorry about that. But the timings are real.

    Business Date	DayEndProcessTypeName	                                                                                StartDtTm	        FinishDtTm	        Minutes
    2054-04-07	Process 1                                                                                       	2019-10-17 17:03:43.580	2019-10-17 17:05:51.017	2.133333
    2054-04-07	Process 2                                                                                        	2019-10-17 17:05:51.017	2019-10-17 17:05:51.017	0.000000
    2054-04-07	Process 3                                                                                        	2019-10-17 17:05:51.033	2019-10-17 17:10:32.953	4.683333
    2054-04-07	Process 4                                                                                        	2019-10-17 17:10:32.953	2019-10-17 17:10:37.143	0.083333
    2054-04-07	Process 5                                                                                        	2019-10-17 17:10:37.143	2019-10-17 17:10:38.220	0.016666
    2054-04-07	Process 6                                                                                        	2019-10-17 17:10:38.220	2019-10-17 17:10:48.563	0.166666
    2054-04-07	Process 7                                                                                        	2019-10-17 17:10:48.563	2019-10-17 17:10:48.563	0.000000
    2054-04-07	Process 8                                                                                           	2019-10-17 17:10:48.563	2019-10-17 17:10:50.643	0.033333
    2054-04-07	Process 9                                                                                       	2019-10-17 17:10:50.643	2019-10-17 17:13:14.893	2.400000
    2054-04-07	Process 10                                                                                        	2019-10-17 17:13:14.893	2019-10-17 17:13:14.923	0.000000
    2054-04-07	Process 11                                                                                       	2019-10-17 17:13:14.923	2019-10-17 17:13:14.970	0.000000
    2054-04-07	Process 12                                                                                       	2019-10-17 17:13:14.970	2019-10-17 17:13:14.970	0.000000
    2054-04-07	Process 13                                                                                       	2019-10-17 17:13:14.970	2019-10-17 17:14:25.407	1.183333
    2054-04-07	Process 14                                                                                       	2019-10-17 17:14:25.407	2019-10-17 17:14:25.407	0.000000
    2054-04-07	Process 15                                                                                       	2019-10-17 17:14:25.407	2019-10-17 17:14:25.487	0.000000
    2054-04-07	Process 16                                                                                       	2019-10-17 17:14:25.487	2019-10-17 17:14:25.563	0.000000
    2054-04-07	Process 17                                                                                       	2019-10-17 17:14:25.563	2019-10-17 17:14:25.563	0.000000
    2054-04-07	Process 18                                                                                          	2019-10-17 17:14:25.563	2019-10-17 17:20:53.283	6.466666
    2054-04-07	Process 19                                                                                       	2019-10-17 17:20:53.283	2019-10-17 17:20:53.300	0.000000
    2054-04-08	Process 20                                                                                       	2019-10-17 17:20:53.300	2019-10-17 17:20:53.317	0.000000
    2054-04-08	Process 21                                                                                       	2019-10-17 17:20:53.330	2019-10-17 17:20:55.067	0.033333
    2054-04-08      Process 22                                                                                      	2019-10-17 17:20:55.067	2019-10-17 17:20:55.127	0.000000

    So that is what I am trying to achieve; automate a way to monitor DayEnd and alert if a problem is detected.


    Thanks for your help.


    • Edited by toastedbread1 Friday, October 18, 2019 3:00 PM numbering
    Friday, October 18, 2019 2:58 PM

Answers

  • As I said in my first post, you cannot schedule the monitoring job on the same Agent instance that runs the DayEnd job. The reason that the DayEnd job has not completed may be that Agent is not running.

    As for the syntax error, this line:

    AND  = 'Job Name and/or Process name'
    I have no idea what you intend here, and nor have SQL Server, so that's why you get a syntax error.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by toastedbread1 Thursday, November 7, 2019 4:02 PM
    Wednesday, October 23, 2019 9:39 PM
    Moderator

All replies

  • The automation part can easily be achieved with SQL Server Agent. You may have to set up one schedules for Monday and another for Thu-Fri.

    For monitoring, now that is a tad more difficult. You would need to have a separate job that performs a WAITFOR UNTIL and at this point checks in the log table that the DayEnd job has been to it. If not, it's time to send mail. But here is the tricky part: it would not be a good idea to run it from the same instance of SQL Server Agent. To wit, the reason the DayEnd job never started might be that Agent has been stopped. And in that case, the monitoring job will also be stalled. That could be avoided with the monitoring job running from another Agent or Windows Task Scheduler.

    An alternative method is to use Service Broker for the monitoring. When the DayEnd procedure has completed one step, it sends a message on a Service Broker queue. That service has also set up a conversation timer, and if the timer elapses before the message arrives, a mail is sent. This is by no means a trivial solution, and may not even be worth it if you are not already using Service Broker.

    You could also consider getting a real monitoring software like Microsoft Operations Manager or Tivoli. I don't have any experience of these, but they may save you from re-inventing the wheel.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, October 18, 2019 9:17 PM
    Moderator
  • So I've been working on this today. So I'm starting on the

          (SELECT '04/07/2054' "DDATE") AS "QDATE"

    statement first. At the moment it runs fine but you have to enter the date manually every time you run. Instead I want to automatically pull the system day.

    This looks like the correct statement to use:

    SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]

    however injecting it into the first statement returns errors. Here is what it looks like so far

    (SELECT 'CONVERT(CHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]' "DDATE") AS "QDATE"

    with error

    Msg 241, Level 16, State 1, Line 1
    Conversion failed when converting date and/or time from character string

    Trying to replace the static date with code that pulls from system date but somewhere the conversion does not work. 101 is the date format I need. Still working on it.

    Thanks!

    Friday, October 18, 2019 9:20 PM
  • (SELECT 'CONVERT(CHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]' "DDATE") AS "QDATE"

    If you wrap something in a single quotes that a string literal, so this will produce the string
       CONVERT(CHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]

    and nothing else.

    The best would be to replace

       (SELECT '04/07/2054' "DDATE") AS "QDATE"

    with
       convert(date, systdatetime()) AS "QDATE"


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, October 19, 2019 9:01 AM
    Moderator
  • OKAY! It works!

    (SELECT CONVERT(DATE, SYSDATETIME(), 101) "DDATE") AS "QDATE"

    Do I need the 101 formatting to display correctly? I added it and runs without errors! Sadly I am getting no output despite the query running clean. If no output is being returned I think the problem would be the formatting is not correct though I am not sure.

    WHERE (CONVERT(DATE,DLOG.BusinessDate) = QDATE.DDATE AND NOT DLOG.DayEndProcessTypeId IN (90,100,110)) OR (CONVERT(DATE,DLOG.BusinessDate) = DATEADD(day,+1,QDATE.DDATE) AND DLOG.DayEndProcessTypeId IN (90,100,110))

    Thanks!

    Monday, October 21, 2019 9:13 PM
  • Do I need the 101 formatting to display correctly? I added it and runs without errors! Sadly I am getting no output despite the query running clean. If no output is being returned I think the problem would be the formatting is not correct though I am not sure.

    The third argument to convert has no importance when you convert to date. A date is a date, and there is no format. The SQL Server uses an internal represenation.

    I don't understand where the displays comes into the picture? Weren't yoou talking about an end-of-day job that needs to know the next day to run for?

    And, oh, you were in the year 2054. sysdatetime is likely to return something in year 2019...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, October 21, 2019 9:57 PM
    Moderator
  • OKAY! So far the code works! I tried this in production last night and looks good. You were right about the year, both times.

    Sorry for the confusion. The full idea is to schedule a SQL query that will identify if a DayEnd has started and then alert if it has not started or stalled out, as in no longer processing when it should be. I'm taking the manual script from my first post and automating to do more.


    So now I need to learn how to get the script to know if a particular cell has been filled with a finish time indicating that the process has completed. Otherwise send an email alerting something is wrong.

     IF (DLOG.FinishDtTm IS NULL -- condition: job hasnt stopped running 
                AND DLOG.StartDtTm IS NOT NULL -- condition: job is currently running
                AND  = 'Job Name and/or Process name' -- TODO: fill in the exact job name to monitor
                )
                PRINT 'JOB LOOKS GOOD';
    ELSE
        EXEC dbo.sp_notify_operator @profile_name = N'MailAlert'
            ,-- TODO
            @name = N'name of operator group goes here'
            ,-- TODO
            @subject = N'Warning: Check DayEnd'
            ,-- TODO
            @body = N'WARNING! Job DayEnd took longer than expected! ';
    GO

    I am currently receiving errors for this.

    Msg 102, Level 15, State 1, Line 29
    Incorrect syntax near '='.
    Msg 156, Level 15, State 1, Line 32
    Incorrect syntax near the keyword 'ELSE'.

    I can't figure out why I am getting errors. Pretty sure the variables are correct so my syntax with the IF ELSE statement is bad. However I am unsure of the AND = 'Write Off' statement. I wasn't sure if I had to specify a row or column instead of just plaintext.

    Additionally I am looking into the Database Mail as well. The picture is regarding the test database I am using.

    The question I have is my code aligned with what is displayed in the database? It doesn't look like Database Mail is specifically set up however if the query sees a problem it will run the dbo.sp_notify_operator which emails everyone in the Operators group the message specified. Is this the correct way to monitor and send an email alert if something goes wrong?

    Many thanks!


    • Edited by toastedbread1 Wednesday, October 23, 2019 8:27 PM sql text
    Wednesday, October 23, 2019 8:26 PM
  • As I said in my first post, you cannot schedule the monitoring job on the same Agent instance that runs the DayEnd job. The reason that the DayEnd job has not completed may be that Agent is not running.

    As for the syntax error, this line:

    AND  = 'Job Name and/or Process name'
    I have no idea what you intend here, and nor have SQL Server, so that's why you get a syntax error.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by toastedbread1 Thursday, November 7, 2019 4:02 PM
    Wednesday, October 23, 2019 9:39 PM
    Moderator
  • Thank you Erland. I understand your response and we took another approach. Marked as solved.
    Thursday, November 7, 2019 4:04 PM