FIX: Misconfiguration of Affinity Values May Cause SQL Server to Not Start (308820)

SYMPTOMS

Starting with SQL Server 2000 Service Pack 1 (SP1) Enterprise Edition, there are new options called I/O Affinity and Connection Affinity that you can use in conjunction with the existing Processor Affinity Mask option, also known as SQL Server Processor Affinity. The options assign specific functions to certain CPUs. Any given CPU should not be assigned to both I/O Affinity and Connection Affinity. If one or more CPUs are mis-configured by assigning them to both I/O Affinity and Connection Affinity, SQL Server may fail to start and error messages similar to the following may appear in the SQL Server error log:

The maximum limit for connections has been reached.
Failed to start system task Fulltext propagator
Open of fault log C:\Program Files\Microsoft SQL Server\MSSQL\log\exception.log failed.

The error message is more likely to occur when in addition to mis-configuring the Affinity settings, the following are also true:

While the MSSearch service, which supports Full-Text searches, runs.

-and-
There are at least 16 processors on the computer.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

To work around this problem, do not set the I/O Affinity and Connection Affinity options.

In most environments setting the I/O Affinity and Connection Affinity options have little or no positive affect on performance. If the options are not set, the problem does not occur.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.

REFERENCES

SQL Server 2000 Books Online; topic: "Affinity Mask Option"

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:

298402 INF: Understanding How to Set the SQL Server I/O Affinity Option

299641 INF: Understanding How to Configure a SQL Server Connection Affinity Mask