BUG: Rebuilding Indexes w/ SQLMaint May Cause Incorrect Database

• Microsoft SQL Server version 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:

   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.

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

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 version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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