November 3, 2008 at 11:39 am
I'm currently working on an application that uses SQL 2005 with SP2 installed.
I have a trigger that needs to start a job, but am having difficulties with this task. I can start the job successfully as long as it is not currently running. To remedy that I have tried a number of different things.
I first created a stored procedure that returns the status of the job. If this procedure returns "idle" then the trigger attempts to start the job. The problem here is with timing. Many times, the status indicates "idle", but the sp_start_job throws an error stating that the job is already running.
The next thing I tried was putting the the sp_start_job within a try/catch. The problem here is there is a known issue with the try/catch logic and the error thrown by sp_start_job is still thrown.
Begin Try
Execute msdb.dbo.sp_start_job @JobName
End Try
Begin Catch
Print 'Unable to start job' -- Never gets here
End Catch
The last thing that I have tried is creating a message and an alert. There are 2 problems with this solution. The 1st being that I can't get it to work on some servers at all. The 2nd being on the servers that it does work on I can't get it to work with out logging enabled. We cannot have this logging this every time that the alert runs because at times this can get into the 10's of thousands.
exec sp_addmessage @msgnum = @MessageNumber
, @severity = 10
, @msgtext = @MessageText
, @with_log = 'False'
exec msdb.dbo.sp_add_alert @name = @AlertName
, @message_id = @MessageNumber
, @severity = 0
, @enabled = 1
, @delay_between_responses = 5
, @notification_message = @MessageText
, @database_name = @DbName
, @job_name = @JobName
Raiserror(60010, 10, 1) -- when logging is disabled the job does not start
Is there something else that I can try or is there something missing in any of my prior attempts at solving this?
TIA
Lauren
November 3, 2008 at 12:20 pm
I think we need more information regarding the process involved and what the job is supposed to do, and how it does it. As you have a trigger starting the job, it is possible that multiple updates could be occuring close enough together that the job doesn't have time to finish between attempts to start the job.
November 3, 2008 at 12:34 pm
I would start by saying that starting a job with a trigger is probably not the right solution - regardless of the problem. A trigger is designed to be acting on a data change and you are already running into the problem that will be the biggest pitfall - concurrent updates will conflict with the job already running.
So, perhaps you can post what you are really trying to accomplish.
Barring that, using Service Broker would allow you to queue a message to start the job. Sending the message to the queue would split the failure of processing the message out of your original transaction. So, if the sp_start_job failed, it would not roll back the original transaction.
November 3, 2008 at 12:43 pm
I'm not sure of the reasoning behind starting the job within the trigger, I'm just trying to find a solution to some problems we are seeing in the field.
The table that the trigger is on is a queue of all the types of calculations that are needed to be done. Once a row is inserted into this table the calculation job needs to be started if it already isn't running. All calculated values need to be as close to real time as possible, so running this job once, twice or even 3 times a day is not an option. Our customers expect that once they change something all calculations will be performed in a timely manner.
I'm aware of the possibility of concurrent inserts into the table. As I said in my previous post, I have tried several things and none seem to work. The best solution I have found is using the Raiserror, but if logging is disabled the job does not start whether from the attempt comes from the trigger or just by issuing the raiserror function in query analyzer.
I have never heard of Service Broker and will have to look into that.
November 3, 2008 at 12:46 pm
Service Broker is actually the tool that MS has given you to process actions in a queue. This sounds like what you should be using.
It is a bit confusing to set up and there is no UI, but there are lots of benefits of learning how to use it, so it is worth the trouble.
November 3, 2008 at 12:55 pm
As already stated, doing this with a trigger is not a good thing.
However, as also mentioned, the fastest way of doing it is using a service broker solution. (i.e. inner / intra sqlserver message queue system)
The sproc that will be launched by your queue will need to take care of the sp_startjob problems.
Maybe this little article can get you on track:
http://www.sqlservercentral.com/articles/Service+Broker/2897/
Another solution which "pollutes" your sqlserver errorlog is by raising an (informative) alert.
Described at http://www.sqlservercentral.com/scripts/Miscellaneous/31032/
To have sqlagent respond faster, this can help you out: http://www.sqlservercentral.com/articles/Administration/3177/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply