January 26, 2009 at 3:06 am
Looking for a bit of advice I suppose rather than a solution to a specific problem, at least at this stage.
I've recently had a new database added to one of the servers I administer which forms the backend to a third-party application my company uses. The installation process created a couple of scheduled jobs, both of which run every minute, which I questioned at the time as I hadn't come across this level of frequency before - we're a construction company, not a nuclear power station! Anyway I was informed it was necessary by the company involved and kind of left it at that.
However, I've been noticing since the database's creation, that there are sporadic login failures against that database in the SQL Agent error log. I'd also noticed, whilst evaluting Red Gate's 'SQL Response' tool recently that the occasional failure of one of these scheduled jobs was failing, although due to the number of times these jobs were executing I, and I hadn't worked out how to see further back in the log, I didn't get around to getting a handle on the failure. I gave up trying to get help from the company responsible for the application. After all they've got their money now and the chap who did the installation only a few weeks ago has now departed.
Anyway, I'm assuming that one of the 'once-a-minute' jobs is failing because occasionally there will be something like a log backup or other process running on the server at the time which means, in turn, there's a delay in the login for the 'once-a-minute' job, hence the login failure.
Does this sound like a reasonable interpretation of what's happening and, more importantly, is the occasional failure acceptable since then jobs in question are running OK the rest of the time?
An additional question if I may be so bold. Are jobs with that kind of frequncy not uncommon, even in non-24/7 and non-mission critical systems?
Regards,
Gordon Feeney.
January 26, 2009 at 5:53 am
Ok, a lot to talk about here.
First, you can increase the number of log entries the SQL Agent retains. Right-click on the SQL Job Agent in management studio and bring up it's properties. In the history section, you can increase the history it holds.
Next, login failures are unlikely to be caused by a log backup. Log backups should be fast and unintrusive. They usually don't cause login problems. You need to get the actual error messages.
As far as if it is ok to have some failures - this is a question for the software vendor. Without knowing what the job is doing, it is impossible to know if not completing now and then is a problem.
Finally, is a job running every minute common? Unfortunately, it is common. Most of the time, jobs that run every minute are polling jobs that are waiting for some data and then processing it. Much of the time, these are misguided attempts to create an event-driven process that is not part of the original transaction. Many of them should have been designed using service broker or a log-reading service such as transactional replication.
January 27, 2009 at 3:28 am
Michael, many thanks for the reponse. Is there a way to get more information as to what caused the login failure than from the error log, which doesn't really give that much away? I also expected to see some info on therrors in the Event log but that doesn't seem to be the case.
Regards,
Gordon.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply