SQL Server Agent job mysteriously stops

[dave4444]

I have a scheduled job that runs every day at 12:45 am.
One of the steps (which executes a stored procedure)is stopping after 30 minutes of run time. SQL Server indicates that the step has completed successfully. When I look at the output table, no rows have been inserted. This has been occurring for the last 3 weeks, consistently, and each time the job stops right at 30 minutes. Other steps in this job have run longer than 30 minutes. When I run the stored procedure interactively, rows are created in the output table and the run times vary from 35 to 50 minutes. This stored procedure has been in use for over a year and there have been no recent changes to the procedure.

Any ideas why this is happening? I am using SQL Server 2000.

 

[Catadmin]

Your title seems to be different than your actual problem. Or did you actually verify that your SQL Server Agent service did stop? If SSA is stopping unexpectedly, there are four things to check, two in the SQL Server Agent properties and two in the SQL Server properties.

First, SQL Server properties, make sure that SQL Server and SQL Server Agent are checked to AutoStart. (General Tab).

Second, SQL Server Agent Properties (second tab) make sure that SQL Server Agent is set to Auto Restart SQL Server if it stops unexpectedly and Auto Restart SQL Server Agent if it stops unexpectedly.

Now, if the SSA did NOT actually stop when your job was running, I think it's time to update your statistics on the database in question and do some Index Tuning maintenance, plus some DBCC checks to make sure everything is still A-Ok with the database and its data & Index pages.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[dave4444]

Sorry, I meant to say that the step stopped executing and not the job itself

 

[Catadmin]

Then follow the last paragraph of advice I gave you (you can look updating stats & Index tuning in Books Online). Once you've got all this one, try and execute the job again and see if the step freezes or not.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[dave4444]

To be precise, the step doesn't freeze, it stops running after 30 minutes and then the job moves on to the next step.

 

[Catadmin]

That definately makes me think it's time for you to check your Estimated Execution Plan of the query you're running in Step 1, then to optimize your DB.

I keep stressing this part because if your stats and indexes are out of whack, it is entirely possible for your job to spin its wheels looking for data that isn't where it thinks it is. Checking your job in QA with the Estimated Execution Plan will give you an idea of where in step one the majority of the hang up is. If it's in a table scan, then it is indicating that you need to optimize your DB.

Just because the job used to do what it was supposed to doesn't mean that it still should continue doing that job. As items get inserted, updated and deleted from your database, the organization of the DB tends to go downhill. It's like keeping a bunch of books on a desk. Initially, the stack looks neat and well kempt. Then people start grabbing books from the middle of the stack, adding more books on top of the stack and inserting files in the books at the bottom. If someone doesn't straighten the stack every once in a while, it's going to fall over into a big pile with papers everywhere, some of the book covers open, some of the books upside down while others are sidewise, etc. And the more stuff that gets added, taken and updated in that pile, the worse it looks.

Eventually, you'll need someone to straighten the pile back up because if you don't, someone could spend 30+ minutes looking for the book they need and if they don't find it, they might assume it's no longer then and go on to their next task of the day.



Until you at least check these options, I can't offer any further advice on how to make your job work.

Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!