I have several regular feeds of help and advice regarding SQL Server and one of those comes from Integrated Services Inc, in the form of the SQLRx Tip of the Month (web:http://www.sqlrx.com / blog: http://sqlrx.wordpress.com/). Sometimes it's stuff I don't need because I have something else in place already but other times its an absolute inspiration.

August is inspiration. The tip this month is a nice script to see when your SQL Server is going to be running scheduled jobs so that you can plan any maintenance accordingly. Brilliant. The script is

USE msdb
GO
SET NOCOUNT ON;
SELECT CONVERT(VARCHAR(20),SERVERPROPERTY('ServerName')) AS ServerName,
j.name AS job_name,
CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))
    +(jh.run_duration * 9 + jh.run_duration % 10000 * 6 
    + jh.run_duration % 100 * 10) / 216e4,108) AS last_run_duration,
ja.next_scheduled_run_date
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
join msdb.dbo.sysjobs_view j ON ja.job_id = j.job_id
WHERE ja.session_id=(SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)
AND j.enabled = 1
ORDER BY job_name;
SET NOCOUNT OFF;
GO

Now this is useful but, as I am in the middle of upgrading to SQL 2008 and am interested in using the new features I got to thinking about how I might be able to add some extra content to the results.

In SQL 2008 the DATETIME data type has been augmented by the TIME and DATE types. DATETIME is still there if that suits your needs but you can now store just a time or just a date rather than having the excess baggage of the DATETIME type.

USE msdb
GO
SET NOCOUNT ON ;
DECLARE @date date = '19000101'
DECLARE @time TIME(0) = '00:00:00'

SELECT  CONVERT(VARCHAR(30), SERVERPROPERTY('ServerName')) AS ServerName,
        j.name AS job_name,
        CONVERT(VARCHAR(10), CONVERT(DATETIME, RTRIM(19000101))
        + ( jh.run_duration * 9 + jh.run_duration % 10000 * 6
            + jh.run_duration % 100 * 10 ) / 216e4, 108) AS last_run_duration,
        ja.next_scheduled_run_date,
        DATEDIFF("d", GETDATE(), ja.next_scheduled_run_date)
              as [Days to next run],
        DATEADD("mi", DATEDIFF("n", GETDATE(), ja.next_scheduled_run_date),@time )
              as [Mins to next run]
FROM    msdb.dbo.sysjobactivity ja
        LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
        join msdb.dbo.sysjobs_view j ON ja.job_id = j.job_id
WHERE   ja.session_id = ( SELECT    MAX(session_id)
                          FROM      msdb.dbo.sysjobactivity
                        )
        AND j.enabled = 1
ORDER BY [Days to next run],[Mins to next run];

SET NOCOUNT OFF ;
GO

As you can see I have added two variables @Time and @Date. I wont concern us with @Date, its use is pretty simple to follow as its very similar to how you would work with it if it was DATETIME type. With the [Mins to next run] column I first use DATEDIFF to identify the number of minutes between GETDATE() and the next scheduled execution and then add that number to the @Time variable that I previously declared as '00:00:00'. Now because it has a TIME data type it works out the maths of turning up the hours value every 60 minutes so no need for us to write further functions. If the DATEDIFF returns a value of 172 then DATEADD("mi",172,@Time) will result in a value of 2:52:00. Nice. It's also a TIME so we could use it in further time and date calculation if necessary. Both scripts shown here are available for download at the top of the page.

I'm going to enjoy implementing this sort of solution in my functions and procedures in the future. Thanks to SQLRx for the script and the inspiration for using the new data type. I would recommend you register for their Tip of the Month and get some good ideas about managing your servers.