High-end disk subsystems may experience error 17883 (810885)

SYMPTOMS

You may receive the following error message:

Error: 17883, Severity: 1, State: 0
The Scheduler 0 appears to be hung. SPID 53, ECID 1, UMS Context 0x04069D90

You may receive this error message when you use any of the following on a high-end disk subsystem:

ALTER DATABASE ADD FILE
CREATE DATABASE
AUTOGROW of files (explicit or implicit)

The 17883 error message was added to SQL Server 2000 Service Pack 3 (SP3). The error message draws attention to a problematic situation with SQL Server schedulers. The error message indicates that some resource is not appropriately yielding the SQL Server scheduler. This problem causes the non-yielding thread to run for extended periods of time, thereby starving other threads on that SQL Scheduler. The server may also become unresponsive. For more information about this error, click the following article number to view the article in the Microsoft Knowledge Base:

319892 New concurrency and scheduling diagnostics added to SQL Server

Note This article discusses one possible cause for the 17883 error message. The workaround that is discussed in this article is only for the cause that is discussed in this article.

CAUSE

One of the possible causes is an unyielding thread.
When you perform any of the operations mentioned in the "Symptoms" sections, SQL Server issues a WriteFile WinAPI call, and then checks the result of the write request. If the operation returns pending, SQL Server appropriately yields that SQL Scheduler.

However, when you perform any of the operations on a large file that physically resides on a high-end disk subsystem, SQL Server issues a write operation and the disk subsystem completes the write operation immediately. Because the disk subsystem completes the write operation so quickly, SQL Server continues to issue new write requests until the whole file operation completes, thereby creating an unyielding thread on the SQL Scheduler. When the SQL Scheduler does not yield for more than 60 seconds, SQL Server generates the 17883 error message.

For the problem to occur, the file size must be so large that even with write operations completing extremely fast, SQL Server continues to post the write requests for more than 60 seconds and has still not reached the end of the operation.

WORKAROUND

There is no direct way to avoid this problem. However, you can schedule all the file operations that are mentioned in the "Symptoms" section to occur during non-peak hours so that the server is not affected by a SQL Server scheduler that has stopped responding until a file operation completes.

RESOLUTION

Service pack information

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

290211 How to obtain the latest SQL Server 2000 service pack