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:
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
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.