x

Performance issues with SQL Job

Team,

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.

Thanks
more ▼

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

lok_taurian gravatar image

lok_taurian
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.

Thanks
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
(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

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x51

asked: Dec 19, 2010 at 10:11 PM

Seen: 817 times

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