Delay in SQLAgent Service startup

Hi,
On a number of servers we are facing issues regarding the SQL Server Agent
service. The service is experiencing a delay during startup, more than the
wait hint period. THe service should ideally be starting in 30 seconds (Or it
seems to say so in the Wait Hint). But it is taking around 1min. Our software
thinks it is a timeout and raises an error.
Following is the SQLAgent log file of when the service is starting:
2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID
2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760 (x86
unicode retail build) : Process ID 1956
2006-11-08 03:30:10 - ? [101] SQL Server [MACHINE_NAME]\[INSTANCE] version
8.00.760 (0 connection limit)
2006-11-08 03:30:10 - ? [102] SQL Server ODBC driver version 3.81.9031
2006-11-08 03:30:10 - ? [103] NetLib being used by driver is DBMSLPCN.DLL;
Local host server is [MACHINE_NAME]\[INSTANCE]
2006-11-08 03:30:10 - ? [310] 2 processor(s) and 1023 MB RAM detected
2006-11-08 03:30:10 - ? [339] Local computer is [MACHINE_NAME] running
Windows NT 5.0 (2195) Service Pack 3
2006-11-08 03:30:10 - ? [124] Subsystem 'TSQL' successfully loaded (maximum
concurrency: 20)
2006-11-08 03:30:40 - ? [124] Subsystem 'CmdExec' successfully loaded
(maximum concurrency: 10)
2006-11-08 03:30:40 - ? [124] Subsystem 'Snapshot' successfully loaded
(maximum concurrency: 100)
2006-11-08 03:30:40 - ? [124] Subsystem 'Distribution' successfully loaded
(maximum concurrency: 100)
2006-11-08 03:30:40 - ? [124] Subsystem 'Merge' successfully loaded (maximum
concurrency: 100)
2006-11-08 03:31:10 - ? [124] Subsystem 'ActiveScripting' successfully
loaded (maximum concurrency: 10)
2006-11-08 03:31:10 - ? [124] Subsystem 'QueueReader' successfully loaded
(maximum concurrency: 100)
2006-11-08 03:31:10 - ? [124] Subsystem 'LogReader' successfully loaded
(maximum concurrency: 25)
2006-11-08 03:31:10 - ? [129] SQLAgent$[INSTANCE] starting under Windows NT
service control
2006-11-08 03:31:10 - + [260] Unable to start mail session (reason: No mail
profile defined)
2006-11-08 03:31:10 - ? [174] Job scheduler engine started (maximum worker
threads: 0)
2006-11-08 03:31:10 - ? [146] Request servicer engine started
2006-11-08 03:31:10 - ? [167] Populating job cache...
2006-11-08 03:31:10 - + [396] An idle CPU condition has not been defined -
OnIdle job schedules will have no effect
2006-11-08 03:31:10 - ? [133] Support engine started
2006-11-08 03:31:10 - ? [193] Alert engine started (using Eventlog Events)
2006-11-08 03:31:10 - ? [168] There are 0 job(s) [0 disabled] in the job cache
2006-11-08 03:31:10 - ? [170] Populating alert cache...
2006-11-08 03:31:10 - ? [171] There are 9 alert(s) in the alert cache
---
The machine is a very high spec machine and there's a lot of cpu/memory
available.
ThanksIt looks like it is hanging on ActiveScripting which is part of DTS. Does
this help you to resolve it?
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kunal" <Kunal@.discussions.microsoft.com> wrote in message
news:C42254BF-88A3-49E5-A461-27B98EF8687D@.microsoft.com...
> Hi,
> On a number of servers we are facing issues regarding the SQL Server Agent
> service. The service is experiencing a delay during startup, more than the
> wait hint period. THe service should ideally be starting in 30 seconds (Or
> it
> seems to say so in the Wait Hint). But it is taking around 1min. Our
> software
> thinks it is a timeout and raises an error.
> Following is the SQLAgent log file of when the service is starting:
> 2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760
> (x86
> unicode retail build) : Process ID
> 2006-11-08 03:30:10 - ? [100] Microsoft SQLServerAgent version 8.00.760
> (x86
> unicode retail build) : Process ID 1956
> 2006-11-08 03:30:10 - ? [101] SQL Server [MACHINE_NAME]\[INSTANCE] version
> 8.00.760 (0 connection limit)
> 2006-11-08 03:30:10 - ? [102] SQL Server ODBC driver version 3.81.9031
> 2006-11-08 03:30:10 - ? [103] NetLib being used by driver is DBMSLPCN.DLL;
> Local host server is [MACHINE_NAME]\[INSTANCE]
> 2006-11-08 03:30:10 - ? [310] 2 processor(s) and 1023 MB RAM detected
> 2006-11-08 03:30:10 - ? [339] Local computer is [MACHINE_NAME] running
> Windows NT 5.0 (2195) Service Pack 3
> 2006-11-08 03:30:10 - ? [124] Subsystem 'TSQL' successfully loaded
> (maximum
> concurrency: 20)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'CmdExec' successfully loaded
> (maximum concurrency: 10)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Snapshot' successfully loaded
> (maximum concurrency: 100)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Distribution' successfully loaded
> (maximum concurrency: 100)
> 2006-11-08 03:30:40 - ? [124] Subsystem 'Merge' successfully loaded
> (maximum
> concurrency: 100)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'ActiveScripting' successfully
> loaded (maximum concurrency: 10)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'QueueReader' successfully loaded
> (maximum concurrency: 100)
> 2006-11-08 03:31:10 - ? [124] Subsystem 'LogReader' successfully loaded
> (maximum concurrency: 25)
> 2006-11-08 03:31:10 - ? [129] SQLAgent$[INSTANCE] starting under Windows
> NT
> service control
> 2006-11-08 03:31:10 - + [260] Unable to start mail session (reason: No
> mail
> profile defined)
> 2006-11-08 03:31:10 - ? [174] Job scheduler engine started (maximum worker
> threads: 0)
> 2006-11-08 03:31:10 - ? [146] Request servicer engine started
> 2006-11-08 03:31:10 - ? [167] Populating job cache...
> 2006-11-08 03:31:10 - + [396] An idle CPU condition has not been defined -
> OnIdle job schedules will have no effect
> 2006-11-08 03:31:10 - ? [133] Support engine started
> 2006-11-08 03:31:10 - ? [193] Alert engine started (using Eventlog Events)
> 2006-11-08 03:31:10 - ? [168] There are 0 job(s) [0 disabled] in the job
> cache
> 2006-11-08 03:31:10 - ? [170] Populating alert cache...
> 2006-11-08 03:31:10 - ? [171] There are 9 alert(s) in the alert cache
> ---
> The machine is a very high spec machine and there's a lot of cpu/memory
> available.
> Thanks

Delay in package starting when running from SQL Agent

Hi,

I wonder if anybody can shed any light on this problem. I have a SQL Agent job which has three steps, each step runs an SSIS package.

The job is scheduled to start at 11.00 pm, which is does successfully. However, it has been taking between 2 and 3 hours to run, which is way longer than it should.

When I've looked at the logging, I've found that the although the job starts at 11.00 pm, the first package (in job step 1) does not start executing until about 11.30. It finishes in about 5 minutes, there is then about an hour delay before the second package (in job step 2) starts. This finishes in about 10 minutes, then there is another hour delay before the third package (in job step 3) starts.

I've tried configuring the steps as SSIS jobs, and also as cmd jobs using dtexec, both exhibit the same behaviour.

Any ideas about what could be causing this delay? The packages are stored in msdb on the same server as the SQL Agent job, if that makes any difference.

Thanks,

Sam

That sounds very strange. Although I'd guess its a SQL Server Agent problem rather than SSIS.

Can you replace the steps with something else - some simple command-line calls for example, and see if the same thing happens?

Do the log fiels for SQL Server Agent and SSIS tie up? i.e. The package may start 30 minutes late but did the job step start 30 minutes late (there's an important distinction here)?

-Jamie

|||

hi sam, I can think over that problem is that your sql agent is very busy attending other jobs ?

|||

Jamie,

Thanks for the reply, I will try the job with a couple of simple calls.

The log fields do not tie up - each job step is starting well before it's package starts.

Sam

|||

Enric,

Thanks for the reply, but this is the only job on the server at the moment, so that shouldn't be causing a problem.

Sam

|||

sam2005 wrote:

Jamie,

Thanks for the reply, I will try the job with a couple of simple calls.

The log fields do not tie up - each job step is starting well before it's package starts.

Sam

If that is the case then I would suggest that the delay is caused by the package going through validation. Set DelayValidation=TRUE on the package to see if this removes the delay. If it doesn't, set DelayValidation=TRUE on all your containers and tasks and see if this removes the delay.

If this solves the problem then you know that it is the validation step that is causing the delay. Try doing what i suggested above and then reply here and we'll take it from there!

-Jamie

|||

this is probably a longshot...

do you see this problem when you run package in bi studio?

is it possible that the service startup is slow?

there is a kb article that talks about problem in sp1

http://support.microsoft.com/?kbid=918644

|||

The DelayValidation at the package level, as suggested by Jamie, seems to have done the trick. I also found that there was a msmsgs.exe process running which was constantly using half the processor - killing this has sped things up even more.

Would the DelayValidation setting have any other impact on the package?

Sam

sql