Job Activity Monitor showing that a job/step is still running even though the job has completed (without running all steps)

This is one of those freaky scenarios where you just pull your hair out…this morning it happened again (second time).
My ETL did ran, but the Job Activity Monitor is showing that it’s still running somehow:

sql-agent-monitor-is-showing-running-while-job-has-finished-stopped-first-step


Turns out that it just completed Step 1 and after that stopped…
The thing is that when you dive deep into the logging, you notice that ‘Step 0 (Job Outcome)‘ is missing and for some reason SQL Agent decided it could stop after Step 1….WTF
(and yes, it suppossed to go to the next step)

sql-agent-monitor-is-showing-running-while-job-has-finished-stopped-first-step-job-history-query-step-0


The Query:


SELECT
j.name AS JobName
,j.job_id AS JobId
,CAST(DATEADD(S,(run_time/10000)*60*60 /* hours */
+((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time - (run_time/100) * 100) /* secs */
,CONVERT(DATETIME,RTRIM(run_date),113))
AS DATETIME) AS ExecutionTime
,h.step_id AS StepId
,h.step_name AS StepName
,h.run_status AS StatusId
,StatusDesc = CASE
WHEN h.run_status=0 THEN 'Failed'
WHEN h.run_status=1 THEN 'Succeeded'
WHEN h.run_status=2 THEN 'Retry'
WHEN h.run_status=3 THEN 'Cancelled'
ELSE 'Unknown'
END
,s.on_success_action
,ActionDesc = CASE
WHEN s.on_success_action = 1 THEN 'QuitWithSuccess'
WHEN s.on_success_action = 3 THEN 'GotoNextStep'
ELSE 'http://technet.microsoft.com/en-us/library/ms142620(v=sql.105).aspx'
END
,h.*
FROM dbo.sysjobs j
INNER JOIN dbo.sysjobhistory h ON j.job_id = h.job_id
LEFT JOIN dbo.sysjobsteps s ON j.job_id = s.job_id AND h.step_id = s.step_id
WHERE DATEADD(S,
(run_time/10000)*60*60 /* hours */
+((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time - (run_time/100) * 100) /* secs */,
CONVERT(DATETIME,RTRIM(run_date),113)) >= DATEADD(d,-2,GetDate())
AND j.name IN ('DWH_TEST (daily): *Step 2')
ORDER BY 1,ExecutionTime DESC



And this time I’ve decided to at least build some kind of mechanism to detect of prevent it.
After a couple of hours of brainstorming and testing things, I found that the shitty thing is is that there’s no ‘BatchId’ of some sort that combines the job_id with step_id and run_date/run_time in dbo.sysjobhistory.

So I figured that during the previous maintenance/ETL window, a SQL Agent Job should have a Step 0 when it has run (skipping Step 0 would indicate that freaky scenario may have happened).
Here’s what I’ve came up with:

sql-agent-monitor-is-showing-running-while-job-has-finished-stopped-monitoring-query-results

LastStepExecutionTime should have taken place in the same (last) maintenance/ETL window as Step0LastExecutionTime.


The Query:

WITH SourceData
AS
(
SELECT
j.name
,h0.Step0LastExecutionTime
,h.LastStepExecutionTime
FROM dbo.sysjobs j
INNER JOIN
(
SELECT
h.job_id
,MAX(
CAST(DATEADD(S,(run_time/10000)*60*60 /* hours */
+((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time - (run_time/100) * 100) /* secs */
,CONVERT(DATETIME,RTRIM(run_date),113))
AS DATETIME)
) AS LastStepExecutionTime
FROM dbo.sysjobhistory h
WHERE run_status = 1 --Succeeded
GROUP BY
h.job_id
) AS h ON j.job_id = h.job_id
LEFT JOIN
(
SELECT
h.job_id
,MAX(
CAST(DATEADD(S,(run_time/10000)*60*60 /* hours */
+((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
+ (run_time - (run_time/100) * 100) /* secs */
,CONVERT(DATETIME,RTRIM(run_date),113))
AS DATETIME)
) AS Step0LastExecutionTime
FROM dbo.sysjobhistory h
WHERE h.step_id = 0 --0 = (Job outcome)
GROUP BY
h.job_id
) AS h0 ON j.job_id = h0.job_id
--Last/final step should have been executed during the last maintenance window.
WHERE h.LastStepExecutionTime BETWEEN DATEADD(HOUR,1,CAST(CAST(GETDATE() AS DATE) AS DATETIME)) AND DATEADD(HOUR,6,CAST(CAST(GETDATE() AS DATE) AS DATETIME))
--While step 0 (Job outcome) should also have been executed/registered during the last maintenance window.
AND NOT h0.Step0LastExecutionTime BETWEEN DATEADD(HOUR,1,CAST(CAST(GETDATE() AS DATE) AS DATETIME)) AND DATEADD(HOUR,6,CAST(CAST(GETDATE() AS DATE) AS DATETIME))

)

SELECT
s.*
FROM SourceData s --Retrieve more info
ORDER BY 1,2,3



Next:
Build something that will alert you, like sending a database mail or integrate this into one of your maintenance reports.








Advertisements

SSRS: SQL Agent Job Schedule Timeline report

Inspired by the SQLjobvis application, which works great btw.
But I wanted to make it a little neater and (of course) have the ability to schedule it daily 😉

Thus….here’s the result:

sql-agent-job-schedule-timeline-report

It displays the SQL Agent Job History of the past 24-hours, but you might want to increase that or even use Dynamic-SQL with Linked-Servers to have the option to select different SQL Servers.

Important note:
In the query I’ve used the new TRY_CONVERT function of SQL Server 2012 (more info here). In case you want to use this report on 2005 or 2008 (R2), look for the function in the query and remove that part (prefix string for report subscriptions).
You may want to use the attached stored procedure or stick with the report which has the query built-in.
Also in the ZIP-file you find a SQL query that is compatible with SQL Server 2000 (but is of course a bit slower).

If you like the report, show your appreciation by sharing it on your blog or social media.
Have fun with it!

Download:
Download it here


Here’s another report of mine which you might find interesting:
SSRS: SQL Agent Job Status report

sql-agent-jobs-report








SSRS: SQL Agent Job Status report

I’ve created a report that shows a little more information than the SQL Agent Activity Monitor and it can be scheduled via SSRS:

sql-agent-jobs-report

Download:
Click here to download the files:
SSRSSQLAgentJobsReport.zip



Here’s another report of mine which you might find interesting:
SSRS: SQL Agent Job Schedule Timeline report

sql-agent-job-schedule-timeline-report








Failed scheduled reports in SSRS (Query)

How to find failed SSRS report subscriptions? Which includes the SQL Agent Job Id?
Well, you might want to use this query!


SELECT
c.Name AS Report_Name
,C.Path AS ReportPath
,s.ScheduleID AS JobName
,su.Description AS SubscriptionDescription
,su.LastStatus
,su.LastRunTime
FROM dbo.Schedule s
INNER JOIN dbo.ReportSchedule rs
ON s.ScheduleID = rs.ScheduleID
INNER JOIN dbo.Catalog c
ON rs.ReportID = c.ItemID
INNER JOIN dbo.Subscriptions su
ON rs.SubscriptionID = su.SubscriptionID
WHERE LastStatus LIKE '%Failure%'

Thanks to Thomas Mucha.


SQLTimer: a SQL Server Timer

the-science-of-timing

thinking_animated_smiley_emoticon

So…you’ve got a fancy Business Intelligence (BI) solution? Predictive Analytics or perhaps Actionable Analytics? Ever thought about the right time to contact your (potential) customers?



SQLTimer might be a handy addition to your BI Suite!
Like a timer for your lighting, it checks every hour whether it’s the right moment to execute jobs for categories like:

BusinessHour: Indicates whether or not it’s the right time to contact your customer during business hours (included in the SQLTimer script)

Other additional time schedules which are downloadable:
Twitter: Indicates whether or not it’s the right time to contact your Twitter users
FaceBook: Indicates whether or not it’s the right time to contact your FaceBook users
LinkedIn: Indicates whether or not it’s the right time to contact your LinkedIn users
GooglePlus: Indicates whether or not it’s the right time to contact your Google+ users
Pinterest: Indicates whether or not it’s the right time to contact your Pinterest users

In this blogpost, I’ll be diving into the SQLTimer itself and the free time schedule called ‘BusinessHour’ (included in the script).

Download the complete script free on:
SQLTimer – How to install


How it works:
Just like the timer for your lightbulb, it is configured on an hourly basis:

SQL-Timer-Execute-TimeSchedule

The values indicate whether it is a bad, better or even the best time to contact your business on a weekly basis:

BusinessHour values:
0) = Outside of business hours
1) = Inside of business hours
2) = Preferred hours

Important note: SQLTimer is setup on a day-to-day basis and thus you might tweak the time schedule a bit for local holidays or other important days in the year:

SQL-Timer-TimeSchedule

SQLTimer objects:
(An existing database is required)
Let’s have a look at all the SQLTimer objects:

SQL-Timer-Objects

*** Tables ***

timer.TimerLogging
This table contains the logging (when a time schedule was executed (StartTime/EndTime) and what value the time schedule had)

SQLTimer’s logging in timer.TimerLogging:

SQL-Timer-Logging

timer.TimeSchedule
This table actually contains the time schedule itself

timer.TimeScheduleConfiguration
This table is all about the configuration

SQLTimer’s configuration in timer.TimeScheduleConfiguration
In case you would like to change the name of the schema, don’t forget to change the configuration settings:

SQL-Timer-TimeScheduleConfiguration


*** Stored Procedures ***

timer.usp_Hourly_Timer
This SP is the timer itself and needs to be scheduled in SQL Agent to be run on an hourly basis

timer.usp_Select_TimeSchedule
You might want to use the timer.usp_Select_TimeSchedule() stored procedure to analyze the time schedule for the coming week:

SQL-Timer-Execute-Select-TimeSchedule

timer.usp_Execute_BusinessHour_Jobs
This SP will execute the preferred jobs (for example: T-SQL code, SP’s or SQL Agent Jobs) for the time schedule ‘BusinessHour’

Here’s how the timer.usp_Execute_BusinessHour_Jobs looks like.
You need to add your T-SQL code, SP’s or SQL Agent Jobs here that need to be executed if the @TimeScheduleValue reaches a certain value.

SQL-Timer-Execute-BusinessHour-Jobs


*** Install the SQLTimer ***

So far about the SQLTimer itself, it’s time to install it!
1) Download the T-SQL script here: SQLTimer – How to install

2) Unzip the ZIP-file and open the *.sql file in SSMS or SSDT

3) Change the database name and uncomment the T-SQL code:

SQL-Timer-T-SQL-Script

4) Execute the T-SQL script
Output of the script:

SQL-Timer-T-SQL-Script-executed

5) Create a SQL Agent Job (Execute T-SQL statements) which executes the timer.usp_Hourly_Timer stored procedure every hour


6) Don’t forget to update the time schedule with your local (company) holidays 🙂
Table: timer.TimeSchedule


Websites:
SQLTimer
BumbleBI IT


ValueFormat function – SSRS Format Mask

This function really comes in handy when you’re working on a report that is able to display numbers/currencies and percentages.

ValueFormat-Code

ValueFormat-Results

Code:

CREATE FUNCTION [dbo].[ValueFormat]
(
   @Value FLOAT --You might want to replace this by DECIMAL(19,4) if it needs to be accurate!
   ,@Character VARCHAR(1)
)
RETURNS VARCHAR(20)
AS
BEGIN

   DECLARE @ValueFormat VARCHAR(20)
   SET @ValueFormat =
   (
   SELECT CASE
      WHEN (@Value) < -100000000000 and @Character = '#' THEN '#,##0.00M'
      WHEN (@Value) < -1000000000 and @Character = '#' THEN '#,##0.00M'
      WHEN (@Value) < -10000000 and @Character = '#' THEN '#,##0.00M'
      WHEN (@Value) < -1000000 and @Character = '#' THEN '#,##K'
      WHEN (@Value) < -10000 and @Character = '#' THEN '#,##K'
      WHEN (@Value) < -1000 and @Character = '#' THEN '#,##K'
      WHEN (@Value) < -1 and @Character = '#' THEN '#,##0.00'
   
      WHEN (@Value) < 1 and @Character = '#' THEN '#,##0.00'
      WHEN (@Value) < 10000 and @Character = '#' THEN '#,##K'
      WHEN (@Value) < 1000000 and @Character = '#' THEN '#,##K'
      WHEN (@Value) < 10000000 and @Character = '#' THEN '#,##0.00M'
      WHEN (@Value) < 1000000000 and @Character = '#' THEN '#,##0.00M'
      WHEN (@Value) < 100000000000 and @Character = '#' THEN '#,##0.00M'
   
      WHEN (@Value) < 0 and @Character = '%' THEN '0.##%'
      WHEN (@Value) < 1 and @Character = '%' THEN '0.##%'
   END
   )
   RETURN @ValueFormat

END

Check SQL Server Agent status on all SQL Servers

There are two easy checks to verify if the SQL Agent service is running:

SQL-Agent-Status-Code

SQL-Agent-Status-Results

#1) By using the build-in DM-view:

SELECT *
FROM master.sys.dm_server_services

(last_startup_time is always NULL so it seems, Microsoft is working on it? more info)

#2) By querying it (script by Colin Stasiuk):

IF EXISTS (
   SELECT 1
   FROM master.dbo.sysprocesses
   WHERE program_name = N'SQLAgent - Generic Refresher'
   )
      BEGIN
         SELECT @@SERVERNAME AS 'InstanceName', 1 AS 'SQLServerAgentRunning'
      END

ELSE
   BEGIN
      SELECT @@SERVERNAME AS 'InstanceName', 0 AS 'SQLServerAgentRunning'
   END