FIX: CREATE INDEX Statement with Parallel Plan May Stop Responding If Disk Space Is Almost Full (317710)

SYMPTOMS

The processor (CPU) usage may spike between 50% and 100% and the process may stop responding for a CREATE INDEX statement if all these conditions are true:

SQL Server 2000 Service Pack 1 (SP1) is the current service pack version.
Execution of this code on the database reports a negative unallocated space:
```
EXEC sp_spaceused
					
```
There is only a small amount of space available in each file.
The server has four (4) or more processors and the max degree of parallelism option is set between 4 and 8 or 0 (use all processors).
A parallel plan is chosen for the CREATE INDEX statement.

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, run this code to correct the available space information in the sysindexes table:

 EXEC sp_spaceused @updateusage = 'TRUE'

After no negative unallocated disk space is reported, the CREATE INDEX statement terminates with the following error message if there is not enough disk space:

Server: Msg 1105, Level 17, State 2, Line 1 Could not allocate space for object 'index_name' in database 'dbname' because the 'PRIMARY' filegroup is full. The statement has been terminated.

MORE INFORMATION

If you observe all of the items described in the "Symptoms" section of this article, use these steps to identify the threads that are no longer responding:

Run this code:
```
EXEC sp_who
					
```
In the sp_who stored procedure output, look for each row with a CREATE INDEX statement in the cmd column, and then note the values in the spid and status columns.
Run this code:
```
use master
go
SELECT * FROM sysprocesses
					
```
Locate the rows in the sysprocesses system table output for the system process IDs (SPIDs) you identified in the previous step, and note the values in the lastwaittype, cpu and physical_io columns. Run the query several times over a couple of minutes time. If the cpu and physical_io columns do not change, and the lastwaittype continues to run, the SPID is no longer responding.

Run the following Transact-SQL command for one of the threads that is no longer responding and you will see information similar to this stack dump:

NOTE: The following DBCC command (DBCC STACKDUMP) is unsupported, and may cause unexpected behavior. Microsoft cannot guarantee that you can solve problems that result from the incorrect use of this DBCC command. Use this DBCC command at your own risk. This DBCC command may not be available in future versions of SQL Server. For a list of the supported DBCC commands, see the "DBCC" topic in the Transact-SQL Reference section of SQL Server Books Online.

DBCC stackdump (-1, [spid#]) -- where the [spid#] belongs to one of the not responding threads

Short Stack Dump
004018EF Module(sqlservr+000018EF) (LatchBase::Release+0000001B)
0083D250 Module(sqlservr+0043D250) (ExtentAllocator::AllocateExtents+00000160)
0083B9E3 Module(sqlservr+0043B9E3) (CBulkAllocator::AllocateExtent+00000066)
0083B7F2 Module(sqlservr+0043B7F2) (CBulkAllocator::AllocatePageId+0000004D)
0083B843 Module(sqlservr+0043B843) (CBulkAllocator::AllocateLinkedAndFormattedLeafPage+0000001E)
0083A941 Module(sqlservr+0043A941) (CIndBuild::AllocateNextIndexPage+0000000E)
0083A7DD Module(sqlservr+0043A7DD) (CIndBuild::InsertRow+0000003F)
007EBA24 Module(sqlservr+003EBA24) (RowsetCreateIndex::InsertSortRow+00000171)
006D045A Module(sqlservr+002D045A) (CQScanParallelCrtIdx::GetRow+00000071)
006EC61A Module(sqlservr+002EC61A) (CQScanXProducer::Open+000000A0)
006EC1FD Module(sqlservr+002EC1FD) (FnProducerThread+0000020E)
0053C403 Module(sqlservr+0013C403) (subproc_main+000000C2)
41073379 Module(UMS+00003379) (ProcessWorkRequests+0000024A)
41073071 Module(UMS+00003071) (ThreadStartRoutine+000000BD)
7800A27B Module(MSVCRT+0000A27B) (beginthread+000000CE)
77E8758A Module(KERNEL32+0000758A) (SetFilePointer+0000018A)