Or alternative method:

1. Create a procedure which returns a list of the jobs which "are running longer than defined SLA"

2. Add a bcp command to your .BAT file which runs that procedure and saves the output into a file.

3. Check the size of the file. If it is not empty - execute the rest of the .BAT (option - CALL ???.BAT ) to raise tickets for each line in the file.

4. Drop the file.

You may turn it into a Windows task running every 5 minutes or make it a service - your choice.

But you better stay away from calling .BAT files from SQL Server.

If something goes wrong during execution ("overwrite the file Y/N", "No such folder, do you want to create it?", "Delete read-only file, are you sure?", lack of disk space or anything else) - SQL does not have means to handle errors raised in cmd shell. You job will be stuck, will be sitting quietly wayting for command shell to complete it task which is waiting for console input in a session with hidden console, no long running jobs will be discovered, no tickets raised, everybody's happy, until customers start to complain about the system being down for half of the day. 🙂

From my experiense - this scenario materialises every time when somebody creates an SQL task calling a .BAT file.

No exclusions so far.

If they kill the job when it's not finished within, say, a minute or so, they end up killing the Windows server with thousands of command shells started on the background and waiting for a human input.