The information in this article applies to:
BUG #: 15618 (6.50) SYMPTOMSWhen creating a nonclustered index on a table with SQL Server 6.5, the indexing process will block all activity in the table, including selects, until the indexing is completed. WORKAROUNDGenerate nonclustered indexes in off-hours when users are not accessing the table. MORE INFORMATIONWhen creating a nonclustered index, SQL Server takes an SH_TABLE lock on the underlying table to prevent changes to the data. This lock can be seen by looking at the output of the sp_lock stored procedure. SQL Server is also locking an internal data structure during the indexing process, which also prevents select operations from accessing the table. The selecting process may not be killed during this time, and will show as blocked with a waittype of 0x0012. The waittype can be seen by looking at the row in sysprocesses for the corresponding spid. STATUSMicrosoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem does not occur in SQL Server 6.0, and has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.Additional query words: create nonclustered index block
Keywords : kbnetwork SSrvGen kbbug6.50 |
Last Reviewed: April 2, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |