Monday, April 13, 2015

SQL Server agent job running at this moment.

SQL Server Agent has a lot of great information stored in the system tables and the GUI provides a nice way of retrieving and viewing the data, but it does not always give you the data the way you would like it displayed.  From a high level it is nice to see the current status of all jobs and the last run status of the jobs, but what if you want to see additional information or the data displayed in a different format.  On a one by one basis you can get this information from the GUI, but it is not very easy to correlate this data across jobs or even across different time periods.

There are probably a million reasons why you could want to try and track down what job(s) might have been running at a particular time. Most of them, of course, will be due to various performance issues, side effects, and other problems that you might end up observing on one of your servers at a given time—and having the ability to quickly rule out a SQL Server Agent Job (or know that it was the culprit) is therefore, a godsend.

One of the things SQL Server doesn’t make all that easy is to know what SQL Agent jobs are running.

exec msdb.dbo.sp_help_job @execution_status=1

This little thing tells precisely which SQL Agent jobs are running at the moment, and that fills a great need in a lot of cases to all the DBA's


No comments:

Post a Comment