Article ID: 175789
Article Last Modified on 3/14/2006
APPLIES TO
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q175789
BUG #: 16955 (WINDOWS: 6.5)
SYMPTOMS
When you attempt to rebuild indexes with the Database Maintenance Wizard (Sqlmaint.exe) or execute DBCC DBREINDEX on at least three tables that have at least one index per table, and then attempt to use sp_dboption to put that database in single user mode, you will get the following error:
CAUSE
When the Database Maintenance Wizard is run directly or as a scheduled task that requests a rebuild of indexes, such as the following, it actually executes a DBCC DBREINDEX for each table in that database:
sqlmaint -D pubs -RebldIdx 10 -Rpt c:\temp\Pubs_maint.rpt
For an example script, see the MORE INFORMATION section of this article.
WORKAROUND
To work around this problem, put the database in single user mode before running the Database Maintenance Wizard (Sqlmaint.exe). Also, you can clear the usage count by stopping and restarting SQL Server.
STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:
197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a
For more information, contact your primary support provider.
MORE INFORMATION
The following SQL script demonstrates the problem:
use master go sp_dboption pubs, single, false go use pubs go dbcc dbreindex('dbo.authors', null, 0, sorted_data_reorg) go dbcc dbreindex('dbo.discounts', null, 0) go dbcc dbreindex('dbo.employee', null, 0, sorted_data_reorg) go use master go waitfor delay '00:00:01' go sp_dboption pubs, single, true go
Additional query words: sqlmaint dbreindex
Keywords: kbbug kbfix kbusage KB175789