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








T-SQL: How to generate a set of dates (time dimension) in a View itself?

Today I wanted to add all Saturdays and Sundays to our so called NonBusinessDays-table.
An easy join with a Time dimension/table would be fairly simple, but it also creates a dependency between the View and the Table itself. I didn’t want that, what I wanted to accomplish is to generate that mini Time dimension in the View itself (so without any other dependencies except for the actual so called NonBusinessDays-table).

So imagine something like:

SELECT * FROM dbo.tbl_NonBusinessDays
UNION
(
SELECT 'all Saturdays and Sundays between 2010 and 2020'
)

Normally a simple query like this would do the trick (inspired by this thread):

WITH AllWeekendDates(WeekendDate)
AS
(
SELECT CAST('2010-01-01 00:00:00.000' AS DATETIME) --From the year 2010
UNION ALL
SELECT DATEADD(DAY,1,WeekendDate) FROM AllWeekendDates
WHERE DATEPART(YEAR,DATEADD(DAY,1,WeekendDate)) < 2020 --Until the year 2020
)

SELECT
WeekendDate
,DATENAME(WEEKDAY,WeekendDate) AS DayName
FROM AllWeekendDates
WHERE DATENAME(WEEKDAY,WeekendDate) IN ('Saturday','Sunday')
OPTION (MAXRECURSION 0)

The results:

CTE-WeekendDays-Results

But! You’re not allowed to use the OPTION-statement in a View 🙂

OPTION (MAXRECURSION limit) is not allowed in view and UDF definitions

An easy thing to do is to move the ‘OPTION (MAXRECURSION 0)’ from inside the View to the select query that’s using the View:
http://benchmarkitconsulting.com/colin-stasiuk/2010/04/12/maxrecursion-with-a-cte-in-a-view/

Like this:

SELECT [WeekendDate]
FROM [dbo].[vw_WeekendDates]
OPTION (MAXRECURSION 0)

A trick that I found while Googling is to use numbers (like for example the surrogate key of a large table or by using ROW_NUMBER()):
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139267 (bottom post)

SELECT DATEADD(d,number,'9/11/2009')AS Date
FROM master..spt_values
WHERE number BETWEEN 1 AND
DATEDIFF(d,'9/11/2009',GETDATE())
AND type = 'P'

Now, let’s combine the original query (that included the OPTION (MAXRECURSION 0) statement) with this fancy trick.
The ideal query to use in a View will be this one:


SELECT DATEADD(d, [Row],'2010-01-01') AS Date --From the year 2010
FROM (
SELECT TOP 10000 [Row] = ROW_NUMBER() OVER(ORDER BY v1.Number ASC)
FROM master.dbo.spt_values v1
CROSS JOIN master.dbo.spt_values v2
) d
WHERE DATENAME(WEEKDAY, DATEADD(d, [Row],'2010-01-01')) IN ('Saturday','Sunday')
AND DATEPART(YEAR, DATEADD(DAY, 1, DATEADD(d, [Row],'2010-01-01'))) < 2020 --Until the year 2020

The results:
WeekendDays-from-View-Results








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