Performance issues with SQL Job


I am facing a strange problem. We have a End of Day job in our system. in about 5-7 days in a month, This job fails in about 70 locations indicating Timeout expired error.

When the job runs the next day after failure, it picks and process the data for two days (The previous day - where the job failed) and the current day. and surprisingly, the job completes with in three minutes !!

Experts advice required on the root cause for this issue and the resolution for the same.


more ▼

asked Dec 19, 2010 at 10:11 PM in Default

avatar image

31 3 3 3

We need a little more information about what the job is doing. You mention 70 locations, does the job run on one server or on 70 servers?

What does the job do? SSIS, T-SQL, Batch file?

Does the job fail at the same time on the same day each month? Is anything else running at that time that could be contending for resources?

What version and edition of SQL Server is this running on?

Dec 19, 2010 at 11:44 PM WilliamD

The job runs on multiple servers (nearly 1200 of them). every month, there is a failure on 70 locations(average) out of 1200 locations.

The job purpose is simple - load data from transaction tables into reporting tables. There are about 15 tables and we have separate SPs to load each table. We have a wrapper SP which calls all of these 15 SPs. The job calls the wrapper SP.

The job fails randomly. There is no specific pattern for failure. failure happens on both SQL 2000 and SQL 2005. There are no other jobs running when this job executes.


Dec 20, 2010 at 12:22 AM lok_taurian

They are loading transaction tables into reporting tables - are these reporting tables on each server, or a central reporting system?

Do you get information on what exactly is timing out?

Dec 20, 2010 at 12:32 AM WilliamD

Data is loaded into respective servers. Cetnral reporting system is not involved here. Time out generally occurs after 30 minutes. My doubt is how can it take 30 minutes, when it can complete processing double the data (The next day) with in Three minutes ?

Dec 20, 2010 at 12:47 AM lok_taurian

You will need to run a trace to see what is happening on the systems at the time of the timeout. Are you sure you are not running anything else on the system at the same time? Backups, copy jobs. It may not be in SQL Server, but some other external process - check with the systems/network/san admins to make sure they aren't doing something at that time.

Dec 20, 2010 at 02:06 AM WilliamD
show all comments (comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Are you able to alter the schedule that this job runs on? I would suggest maybe moving it forward or back by 30 minutes or so and see if it resolves the issue. If so then its a conflict - maybe locks/blocks - with another job running at the same time. Its only a stab in the dark but might get you a resolution quickly...

more ▼

answered Dec 20, 2010 at 05:33 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 19, 2010 at 10:11 PM

Seen: 933 times

Last Updated: Dec 19, 2010 at 10:11 PM

Copyright 2018 Redgate Software. Privacy Policy