Timely slowdown of a job process that runs every night at 2

[youngcougar]

Here's the situation:

We have an SQL 2000 Server. We run a job everynight at 2:00 AM. On Monday, Wednesday, Friday and Sunday 2 steps start at 2:10, and finish in approximatly 1 hour. On Tuesday, Thursday and Saturday these 2 steps take 3 hours to finish. We don't know why. There are no other jobs running on this server at that time, no tape backups happening, no anti - virus push, we've also checked the event, application, security viewer (logs) to see if something is happening at that time...nothing. Has anyone ever come across this before.......? Or does anyone have any suggestions as to what events or logs we can check to see what the box is doing, as it seems to be right on schedule for when things slow down!

Thanks in advance

YC

 

[mrdenny]

Other things that can slow it down.

AntiVirus Scan
Disk Defrag
System Backup
Anything else that puts a load on the disk or CPU.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[youngcougar]

No Antivirus, could a disk defrag run on the server at the same time and not kill the process? I'll check that.........and I'll check if there is a system back up going on..........any other ideas?

 

[Spyrios]

I'm not sure what type of job your doing, so I'm kind of shooting in the dark here. A possibility is that the statiscs are not updated. If the job is inserting/updating records or the like, it's possible that your database is growing on those days. Increasing the size of the db and the growth percentage should help it in that case.

You probably want to run a trace in sql profiler to see everything that is happening on the server during those periods.

 

[youngcougar]

yes, there are updates and inserts happening, but they also happen on Monday, Wednesday, Friday and Sunday. The actual growth is dependant on our transactional db. The amount of transactions that occur don't increase only on Tues, Thurs, and Saturday. Saturday is our biggest transactional day, with the inserts and updates happening each morning so it would seem logical that Sunday AM the job would be slow, but it's not. I was wondering if there was something that could be running on the OS of the server, that someone might suggest I check, as I'm fairly new to this position, and there is a big learning curve !