SQL: detect when a recurring log (every 30s) is missing

Question

We have an SQL job that runs every 30s where we logged every step (SQL Server 2012)

Sometimes, without any reason, and without any "job failed" message, the job stops and even hangs (behavior that we would like to analyse).

What we would like to achieve is to query out log table and detect when a record is missing (doesn't happens every 30s).

For instance:

CREATION_DATE   MESSAGE
2015-09-17 07:49:38.053 11 : **Fin**
2015-09-17 07:49:02.377 11 : **Fin**
2015-09-17 07:48:32.100 11 : **Fin**
2015-09-17 07:48:01.940 11 : **Fin**
2015-09-17 07:47:32.100 11 : **Fin**
2015-09-17 07:47:01.967 11 : **Fin**
2015-09-17 07:46:31.663 11 : **Fin**
2015-09-17 07:46:01.803 11 : **Fin**
2015-09-17 07:45:31.663 11 : **Fin**
2015-09-17 07:45:02.060 11 : **Fin**
2015-09-17 07:44:31.843 11 : **Fin**
2015-09-17 07:44:01.970 11 : **Fin**
2015-09-17 07:43:22.397 11 : **Fin** <= MUST BE DETECTED (23 minutes between events)
2015-09-17 07:20:01.767 11 : **Fin** <= MUST BE DETECTED (3 minutes between events)
2015-09-17 07:17:01.743 11 : **Fin** 
2015-09-17 07:16:31.777 11 : **Fin**
2015-09-17 07:16:01.690 11 : **Fin**
2015-09-17 07:15:31.733 11 : **Fin**
2015-09-17 07:15:01.807 11 : **Fin**
2015-09-17 07:14:31.683 11 : **Fin**
2015-09-17 07:14:01.793 11 : **Fin**
2015-09-17 07:13:31.853 11 : **Fin**
2015-09-17 07:13:01.840 11 : **Fin**

I hope my question is clear.

Saic Siquot · Accepted Answer · 2015-10-06T15:39:49.323

1

This shows all periods longer than 30 seconds

select     A.CREATION_DATE as Start,  min(N.CREATION_DATE) as Finish
from       logTable  A
left join  logTable  N  on A.CREATION_DATE < N.CREATION_DATE
group by   A.CREATION_DATE
having     datediff(second, A.CREATION_DATE, min(N.CREATION_DATE)) > 30

edited Oct 06 '15 at 15:39

answered Oct 06 '15 at 15:31

Saic Siquot

6,513
5
34
56

Thank for the answer, I am still trying to understand your SQL sentence, but using it is still executing after 4 minutes.... I pasted my current (dirty) solution that works in 5 secs below. – Waza_Be Oct 06 '15 at 15:45
do you have an index on CREATION_DATE? – Saic Siquot Oct 06 '15 at 15:47
I think that is the problem, CREATION_DATE is a simple datetime field with no index. – Waza_Be Oct 06 '15 at 15:48
1

yes, an index is necesary here, also `top 1000` is other important diference. Glad to help – Saic Siquot Oct 06 '15 at 16:10
Unfortunately, I cannot change the database to add an index... Top 100 is removed in current request and works in a few seconds. Still trying to make your code to work and see if I can add the index. Thank a lot. – Waza_Be Oct 06 '15 at 16:20

score 0 · Answer 2 · answered Oct 06 '15 at 15:47

This is my "dirty" solution while trying to make the solution by Luis to work (looks better than mine)

SELECT TOP 1000 [PROCEDURE_NAME]
      ,[CREATION_DATE]
      ,[MESSAGE]
      ,
       DATEDIFF(SECOND,
       (SELECT top 1 [CREATION_DATE] 
                    FROM [mactac].[mactac].[ERROR_LOG] f 
                    WHERE f.[CREATION_DATE]<c.[CREATION_DATE]
                    AND [PROCEDURE_NAME] like '%downl%' 
                    AND [MESSAGE]  LIKE '11 : **Fin**'
                      order by [CREATION_DATE] desc)
                    ,c.[CREATION_DATE]) as DIFF

  FROM [mactac].[mactac].[ERROR_LOG] as c
  WHERE [PROCEDURE_NAME] like '%downl%'
  AND [MESSAGE]  LIKE '11 : **Fin**'
  order by [CREATION_DATE] desc

This solutions displays all the records, and elapsed time :-D But.... I have not figured yet how to add:

AND DIFF > 35

Invalid column name 'DIFF'.

In order to filter longer time

score 0 · Answer 3 · answered Oct 06 '15 at 16:08

Using a CTE makes this pretty easy.

with ordered_logs (creation_date, rnk) as
(
  select creation_date, row_number() over (order by creation_date)
  from your_log_table
)
select first.creation_date, second.creation_date, datediff(ss, first.creation_date, second.creation_date) as SecondsDifference
from ordered_logs first
inner join ordered_logs second on (first.rnk + 1) = second.rnk
where datediff(ss, first.creation_date, second.creation_date) > 30

This orders all the records by their created date then joins the earlier one with the next one (time-wise). Then it just checks if the difference in the creation time is more than 30 seconds.

SQL: detect when a recurring log (every 30s) is missing

3 Answers3