SQL Server: Monitor Long SQL Agent Jobs

In SQL Server, there are various jobs running on the server, each solving a different purpose. Sometimes these jobs run for a long time. The best way to identify and monitor these long running jobs is to make use of a profiler.

Let us assume, you have the following code:

While 1=1
print 1

When you schedule this as a job, the code never finishes it's execution, as the code does not have any condition to break the loop. So it would be a never ending job that may consume full memory to execute the endless loop.

Note: This is just an example to emulate a long running job.

When you run a profiler, you can identify jobs which have ‘Batch Starting’ class with no corresponding ‘Batch Ending’ Class. Also the columns CPU, Reads, Writes etc. will be empty. As you can see in the profiler report below, the row with textdata while 1=1 print 1 is a long running job. See the highlighted row below:

Also if the values of columns CPU, Reads, Writes, etc. are huge in numbers, there is a chance that a job is running for long time. The following is an example of a long running, but which finishes its execution after some time.



As you see in the profiler result, the values for the columns CPU, Reads, Writes etc. are high which means these are also long running jobs



Important Notes:

1. Do not run the above examples in a production server, as they will consume a lot of memory
2. After trying the above examples as a job, make sure to delete those jobs.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan