asked
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
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?
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
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 ?
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.
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...
