FIX: Rebuilding Indexes with SQLMaint May Cause Incorrect Database

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q175789

BUG #: 16955 (WINDOWS: 6.5)

Expand all | Collapse all

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:

Msg 3508, Level 16, State 1, Server NTJTK1, Line 1
Attempt to set 'pubs' database to single user mode failed because the usage count is 4. Make sure that no other users are currently using this database and rerun CHECKPOINT.

Back to the top

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.

Back to the top

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.

Back to the top

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 (http://support.microsoft.com/kb/197177/ ) INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.

Back to the top

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

Back to the top

APPLIES TO

Microsoft SQL Server 6.5 Standard Edition

Back to the top

kbbug kbfix kbusage KB175789

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top