SQL Server Agent is taking long time to start
SQL Server Agent might take long time to start because of slow communications with Certificate Authorities.
If you enable verbose logging for SQL Server agent (-v) and look at the SQL Server agent log you will notice that 'ANALYSISQUERY' subsystem has taken long time to long
2012-02-15 15:42:42 - ? [124] Subsystem 'QueueReader' successfully loaded (maximum concurrency: 800)
2012-02-15 15:47:08 - ? [124] Subsystem 'ANALYSISQUERY' successfully loaded (maximum concurrency: 800)
2012-02-15 15:47:08 - ? [124] Subsystem 'ANALYSISCOMMAND' successfully loaded (maximum concurrency: 800)
Also if you collect dumps during the SQLServer agent startup you will notice the stack like one below.
ntdll!ZwWaitForSingleObject
kernel32!WaitForSingleObjectEx
cryptnet!CryptRetrieveObjectByUrlWithTimeout
cryptnet!CryptRetrieveObjectByUrlW
crypt32!ChainRetrieveObjectByUrlW
crypt32!CCertChainEngine::RetrieveCrossCertUrl
crypt32!CCertChainEngine::UpdateCrossCerts
crypt32!CCertChainEngine::Resync
crypt32!CCertChainEngine::CreateChainContextFromPathGraph
crypt32!CCertChainEngine::GetChainContext
crypt32!CertGetCertificateChain
wintrust!_WalkChain
wintrust!WintrustCertificateTrust
wintrust!_VerifyTrust
wintrust!WinVerifyTrust
mscorsec!GetPublisher
mscorwks!PEFile::CheckSecurity
mscorwks!PEAssembly::DoLoadSignatureChecks
mscorwks!PEAssembly::PEAssembly
mscorwks!PEAssembly::DoOpenHMODULE
mscorwks!PEAssembly::OpenHMODULE
mscorwks!AppDomain::BindExplicitAssembly
mscorwks!AppDomain::LoadExplicitAssembly
mscorwks!ExecuteDLLForAttach
mscorwks!ExecuteDLL
mscorwks!CorDllMainForThunk
mscoree!CorDllMainWorkerForThunk
mscoree!VTableBootstrapThunkInitHelper
mscoree!VTableBootstrapThunkInitHelperStub
SQLAGENT!LoadSubsystem
SQLAGENT!StartSubSystems
SQLAGENT!DumpAndCheckServerVersion
SQLAGENT!ServiceMain
advapi32!ScSvcctrlThreadW
kernel32!BaseThreadInitThunk
ntdll!RtlUserThreadStart
ANALYSISQUERY subsystem has assembly which has an Authenticode signature. When the CLR loads an assembly which has an Authenticode signature, it will always try to verify that signature.
This verification can be quite time intensive, since it can require hitting the network several times to download up to date certificate revocation lists, and also to ensure that there is a full chain
of valid certificates on the way to a trusted root.
If you can’t get to the internet to authenticate signature or want to bypass the Authenticode signature you can try creating a sqlagent.exe.config file with the following xml in Binn directory. This bypasses the check
Create a sqlagent.exe.config file with:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<runtime>
<generatePublisherEvidence enabled="false"/>
</runtime>
</configuration>
Thanks
Karthick P.K