MSSQLWIKI

Karthick P.K on SQL Server

SQL Server Agent is taking long time to start

Posted by Karthick P.K on April 19, 2012

 

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 start

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

About these ads

One Response to “SQL Server Agent is taking long time to start”

  1. […] SQL Server Agent is taking long time to start […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,137 other followers

%d bloggers like this: