Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
missMac
Posting Yak Master
124 Posts |
 Posted - 2008-10-17 : 15:08:24
|
Hello guys,we have a sytems that has custom alerts. That can be setup by users anytime.These alerts basically is setup, are to run at a specified time.PROBLEM:Since these are random alerts. What basically it does is to run a TSQL script. Is it ok...1. To setup a job to scan the alert table every 5mins2. Or to create a job everytime the user enters an alert ?Bear in mind. IF 1. i choosen, will it affect resources ? Having a job run every 5minsIf 2. is choosen. Will it overload the system to have multiple jobs running ?ITS JUST BASICALLY ADVICE ON THE WAY FORWARDThanksxxx  |
|
|
ForkandBeard
Starting Member
10 Posts |
Posted - 2008-10-17 : 17:29:26
|
| A job which executes a query against a table every 5 mins is in no way a resource issue, unless this a big table with no indexes, or the query involves a huge record set.The idea of creating a job at runtime for each user interaction gives me a real headache. I can't see people recommending this one.Define exactly what 'creating an alert' entails, and then find a way of invoking/managing it via a SQL job.Many ThanksMitchellwww.forkandbeard.co.uk |
 |
|
|
missMac
Posting Yak Master
124 Posts |
Posted - 2008-10-17 : 17:37:17
|
| Ok. ThanksOne question though. I noticed you cant run more than one instance of a job. THen what do you do, when you have multiple entries in the schedule table waiting for execution. Since you dont subscribe to option 2 ? |
|
|
|
SimpleSQL
Yak Posting Veteran
85 Posts |
Posted - 2008-10-17 : 23:35:18
|
| Having jobs create at run time, is not a good option. It can make managing those jobs painful.From what I understand you need some action to occur once Alerts are in Alert table.Why not use insert trigger on the Alert table to run whatever T SQL code that you need to run (maybe invoke it as part of Stored procedure, that is the trigger fires stored procedure). This way there is no post job cleanup (in SQL Agent job scenario, you will have to delete those jobs).Also another option is to use Service broker implementation, wherein alerts are put in service broker queue and once alerts are there, a stored procedure is fired to do the required operation.These would be more efficient and scalable implementations than Job. |
|
|
|
missMac
Posting Yak Master
124 Posts |
Posted - 2008-10-18 : 02:58:57
|
| Since the day, i started working with MS SQL, i was adviced against cursors and triggers.Also i tried using a trigger to run some jobs that take time to complete, but the trigger.1. Locks the table2. throws an exception when it takes long to execute |
|
|
|
|
|
|
|
|
Run a job every 5minutes