I was perusing the MSSQL Agent logs and came across a job that kept printing errors every 20 seconds. It has an id with a long number. How can you find what job this is by name?
Here is what the error looks like: “Request to run job 0x17DF82795C5C224CBF7DC88E8E3C4E12 (from Alert 2618) refused because the job is not currently enabled.”
To find the job I must assume that this mess of numbers is a GUID, and therefore it relates to a name?
select * from dbo.sysjobs
I find a job_id that relates to the problem above by just grabbing the last part of the guid without dashes. Here is the real job_id:
I can also look for when this job might fire again:
select top 100 * from sysjobschedules where job_id like ‘%C88E8E3C4E12%’
The job is disabled and there are no schedules for it. So why is it erroring every 20 seconds? It is an alert:
select * from dbo.sysalerts where id = 2618
Someone created a check for blocking transaction job, and fired it upon Blocking Alert. Then they found out that they didn’t want the thousands of emails so they disabled the job. The alert is enabled, calling a disabled job. Error logs get filled now instead of inboxes. The solution was to disable the alert.
So you need to automate some long SQL crunching process in the background? You don’t want slow code and you do not want the browser tied up. You need a fire and forget method. There are several interesting ways to accomplish this. Using ColdFusion 8 you can create a CFthread to hold the process, but we are not at that version yet. We also do not have permission to create CF schedules. I did not want to attempt to consume a webservice from SQL or do anything risky from a security point of view.
My first solution involved scheduling a SQL Job that checks every now and then in a table and fires off based on the values stored there. The problem with this is notifications of when the Job finishes.
Another solid method was to build an AJAX loading screen and use a CFFlush to give most of the page back but leave the long running process as a background part of the page. This also was painful, creating timed out pages and locked tables. More details on this solution at ColdFusionJedi.com.
The final solution I built is similar to the above, but with one interesting difference. I still populate the queue table with the variables I want processed. I still have the stored procedure set up to look in the table and do the work. The trick is to launch a Stored Procedure using the sp_start_job in a table trigger. Sp_start_job has a special quality that it does not need to return a status. Here is what the actual trigger looks like. Simple!
CREATE TRIGGER [dbo].[DoThisLongThing]
EXECUTE msdb..sp_start_job N’LongRunningJobName’ ;
I ran into two gotchas. They both relate to sp_start_job living in another db (msdb). The first error below is an indicator that the job or a child job does not have proper permissions. The database cannot see the job even though it exists and you get something like:
“The specified job_name (‘Your_Job_Here’) does not exist.”
And the other frustratingly cryptic problem:
“The EXECUTE permission was denied on the object ‘sp_start_job’, database ‘msdb’, schema ‘dbo’.”
To solve these, first attempt to authenticate as the web db account using Microsoft SQL Management Studio and perform an insert. This should narrow the problem down. If the web insert fails to fire the trigger with the above errors, here is a path to investigate:
- SQL Agent must be running
- The target db account needs the ‘SQLAgentUserRole’
- Make the target db account owner of the job
If you still end up with errors look into the following:
- Explicitly grant the EXEC permission to the target account
- Set the target database as trustworthy from msdb
- Verify the current version of MDAC on SQL and on CF server
The IT world seems to regurgitate catch phrases and “power words”. This leaves us meager developers with the need to build a list of these things for future reference:
Robust dynamic feature-rich flexible architectures with stylized integrated automated procedure-driven enterprise-wide application-specific configurable intuitive modular adaptive beneficial structures.
This post should get edited frequently with generic phrase banter upon occasion. Feel free to submit more for when creative juices are not flowing.