August 11, 2010 at 2:41 pm
Two scenarios: First is a data pump that processes files. It's currently written in DTS. It was developed to run in an endless loop. One of the first steps of the DTS packages checks if there are any files to process. If no, then it sleeps for a few minutes and starts again.
Scenario two: I have a file watcher process that monitors a directory for files by executing repeatedly via a SQL Agent Job. I want to do something with the files as soon as possible after their received so I have a high execution frequency of this job.
I haven't found a graceful way in scenario one to run a data pump (once its rebuilt in SSIS) in an endless loop. On the contrary, Mostly what I've seen are suggestions to put it on a SQL Agent Job and repeatedly execute it that way.
I'd like to discuss how the users out there utilize SQL Agent Jobs. The good thing about a high frequency job is that if the job is already running it doesn't run it again in another process. I haven't found any articles that indicate how much is "too much" when scheduling a job that you basically want to run continuously?
What are some ways where I could throttle the frequency of a SQL Agent job? i.e. the last file I process was over an hour ago; I'm going to sleep for 15 minutes and then try again.
August 11, 2010 at 6:55 pm
I would create a stored procedure that loops continuously - and put in a WAITFOR DELAY.
Then, create a SQL Agent job that checks to see if the process is started already, if not - start it back up.
Use a table to hold configuration options - and you could possibly use a global temp table to determine whether or not the process is currently running. If the procedure is not running - the global temp table should disappear.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 12, 2010 at 9:42 am
Jeffrey Williams-493691 (8/11/2010)
I would create a stored procedure that loops continuously - and put in a WAITFOR DELAY.Then, create a SQL Agent job that checks to see if the process is started already, if not - start it back up.
Use a table to hold configuration options - and you could possibly use a global temp table to determine whether or not the process is currently running. If the procedure is not running - the global temp table should disappear.
Thanks for the post, Jeffrey; but that still seems to lead to the same problem as I started out with. If I have a SQL Agent Job that checks to see if the process is started already; then I'm going to need to regularly execute that job; which brings us back to how often is "too often" when executing a sql agent job?
The Scheduling tab of SQL Agent Jobs will allow me to set a schedule to run once a second. I have to wonder what would that do to system overhead?
August 12, 2010 at 1:46 pm
You have to take a few things into consideration when deciding how often to run jobs.
1. How long does the job take to complete and how many users will be affected by any delays? If any delays are intolerable, then run only as often as necessary to acheive the desired result.
2. How often do the data/attributes change in the database and is the job critical to the accuracy of data? If the work the job does is time critical then any delays to users may not be an issue.
3. If the job is simply a maintenance job that is not crucial to user success, then running it as little as possible or after hours may be best.
In short, it all comes down to what the job does and whether timeliness of the job's results is critical to the successful operation of the system.
August 13, 2010 at 1:15 am
Personally, I would not implement this as a single task. Instead, I would do something like
- create a file watcher process (VB.Net or C# can do this pretty easily) and run this continuously (possibly as a service). This process would simply write a record into a queue of files to be processed (maybe even use Service Broker)
- use the existing DTS or SSIS packages to process all records (i.e. files) in the queue that have not been processed yet. The DTS or SSIS package would be responsible for marking each file as being processed and possibly for archiving the file is this is required. This package could be scheduled as often as you need. You could even get the file watcher process to start the job or run the package if you wished. If you did that, there may be no need to use SQL Agent at all.
August 13, 2010 at 1:20 am
happycat59 (8/13/2010)
Personally, I would not implement this as a single task. Instead, I would do something like- create a file watcher process (VB.Net or C# can do this pretty easily) and run this continuously (possibly as a service). This process would simply write a record into a queue of files to be processed (maybe even use Service Broker)
This is the route I would go for something like this.
Also, the job frequency depends on the complexity of the job. If the job just checks the os and is not very complex, run it every 10 seconds. Knowing the impact of the job should help to determine frequency and what would be too often for the system.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 13, 2010 at 8:01 am
SQL Agent Job scheduler itself is very light, the minimal interval SQL Server supports natively is 1 minute. If you goal is to let you job response to new files ASAP, then set the schedule to run every 1 minutes.
August 13, 2010 at 8:44 am
yeek li (8/13/2010)
SQL Agent Job scheduler itself is very light, the minimal interval SQL Server supports natively is 1 minute. If you goal is to let you job response to new files ASAP, then set the schedule to run every 1 minutes.
The minimum is 10 seconds with 2008.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 13, 2010 at 12:42 pm
Jerry, I totally agree with you! It all depends on the job and what the desired outcome is?
October 10, 2010 at 8:19 pm
Hi
I haven't used this technology but I saw a webcast about it.
Use Windows WMI events
The following query watches for notification that a file has been added to a folder.
SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE TargetInstance ISA "CIM_DirectoryContainsFile" and TargetInstance.GroupComponent= "Win32_Directory.Name=\"c:\\\\WMIFileWatcher\""
October 11, 2010 at 8:19 am
I'd try putting an SSIS "for loop" sequence container with a variable set to true and never changed and an evaluation expression testing that variable for false. Inside that, a "for each" sequence container to process each file and a sql task to WAITFOR. The package then just runs as a never ending sql agent job
A colleague has done it but I don't have the full details. I think it's been refined to enable the job to be stopped by setting something, maybe a value in a database.
October 11, 2010 at 8:52 am
P Jones (10/11/2010)
I'd try putting an SSIS "for loop" sequence container with a variable set to true and never changed and an evaluation expression testing that variable for false. Inside that, a "for each" sequence container to process each file and a sql task to WAITFOR. The package then just runs as a never ending sql agent jobA colleague has done it but I don't have the full details. I think it's been refined to enable the job to be stopped by setting something, maybe a value in a database.
This is pretty much what I did. I kick off the job to run the SSIS package on a regular schedule. The SSIS package continues to run until all files are processed then ends. If a job is running when it's scheduled to run again, it doesn't hurt anything. If there are no items to process the ssis package just ends without doing anything.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply