Timely slowdown of job process 'bumped' to a different day 1

[youngcougar]

Here was my origional post:

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!

Now:

On Friday we had a 'crash' and the server recovered all of the databases. We halted all of the jobs that would run on Saturday so it would give the server a day to get stable. So we basically cancelled the 'slow' day job (Tues,Thurs,Sat). The funny thing is that when we restarted all of the jobs the slow days have been bumped to Monday, Wednesday and Friday? Anyone have any ideas on this, or come across a 'bug' like this? Does a server cache some metadata every second day? All of the databases get shrunk everyday, and the maintenance happens twice a week.......we haven't changed any of the times the jobs run, just that on a 'slow' day, the job was disabled, and now we have 'new' slow days!

Thanks in advance

 

[SQLSister]

YOu could turn on Profiler and trace what events are happening at night ( I would suggest running the trace only at night as Profiler can really slow down your system. Do a night when the job is slow and a night when it isn't.

What exactly does the job do?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[youngcougar]

It's actually a step in a job of 23 steps. This particular step is run off a sp. 4 tables are truncated first, then the DailySales_Summary are built off of 6 other look up type of tables. The WeeklySales_Summary is then built off of the DailySales_Summary (aggregated), the MonthlySales_Summary is built off the Weekly, and the Yearly is built off of the monthly. I'd love to say that this proc, however cumbersome is the problem, but I've moved other steps to occur in it's place to see if it's the proc~it's not, whatever step we put there, at about 2:00 AM every second day (changed days now), that step runs almost 8 times slower than it should. For example, an 8 minute step takes an hour and 10minutes? All of the inserts at this time happen on the server. They don't have to go accross network traffic to another server to get the information. It has all of us stumped here. No operating system job, as we wouldn't have been able to bump the days the job runs slowly~it would have remained on Tues,Thurs,Sat

We are a little afraid to use the profiler at night (fast or slow) as the execs want their reports first thing in the AM, and things have been a little 'wiggly' lately . This is a data warehouse server, not transactional.

Thank you SQLSister for your reply

 

[SQLSister]

Then I'd run profiler on Friday and Saturday nights. Managers prolly won't be so much in a hurry for reports then.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief