If you want to monitor job executions, usually the easiest way is to use the Job Activity Monitor or the Job History Viewer in SQL Server Management Studio. But sometimes you need to check which jobs are running at a specific point in time. For example, if there was a performance problem yesterday at 02:16:00, you would probably want to check which jobs were running at that time, to see if any job is the cause of the problem.
SQL Server Management Studio doesn’t provide ways to filter according to a specific point in time. You need to manually search for the relevant job executions. If you have a lot of jobs and a lot of job executions, this could be a cumbersome search.
This batch does the work. It displays all the jobs that were in the middle of execution at a specific point in time. All you need to do is to change the value of @SamplingDateTime. The script uses the DATETIME2 data type, which is new in SQL Server 2008, so it won’t run on SQL Server 2005, but it can easily be modified to run on SQL Server 2005 as well.