Huge blocking in SQL Server that is used with Configuration Manager 2007 Server

SQL Server is configured to be used with Configuration Manager 2007 Server and there is blocking observed in SQL Server. The head blocker SPID is executing below query

 

select PkgID, Name, Version, Language, Manufacturer, Description, Source, SourceSite, StoredPkgPath, StoredPkgVersion, ShareName, PreferredAddress, StorePkgFlag, ShareType, Permission, RefreshSchedule, LastRefresh, UseForcedDisconnect, ForcedRetryDelay, DisconnectDelay, IgnoreSchedule, Priority, PkgFlags, MIFFilename, MIFPublisher, MIFName, MIFVersion, SourceVersion, SourceDate, SourceSize, SourceCompSize, ImageFlags, Hash, NewHash, HashVersion, Architecture, ImagePath, PackageType, AlternateContentProviders, SourceLocaleID

From

        SMSPackages AS SMSPackages

Where

        Action <> 3

Order By

        PkgID

 

The headblocker SPID is waiting on ASYNC_NETWORK_IO and host name is the machine running SMS Distribution Manager application.

Other Symptoms

Due to blocking customer may report symptoms in SQL Server like not being able to connect to SQL Server, unable to open SSMS, worker thread exhaustion, long blocking chain etc.

Configuration Manager 2007 Distribution Manager performs URL checking on all of its packages assigned to current site which can take ver long time depending on number of rows in SMSPackages table.

Collaborate with Configuration Manager 2007 team to report this issue.

Follow KB 956194 that fixes this issue by adding a registry key to disable the URL checking by SMS Distribution Manager.

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use (http://go.microsoft.com/fwlink/?LinkId=151500) for other considerations.