You are currently offline, waiting for your internet to reconnect

You must start an instance of SQL Server in single-user mode when you use DBCC CHECKDB/CHECKTABLE with REPAIR options

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

This article was previously published under Q264154
SUMMARY
The correct way to run a DBCC CHECKDB or DBCC CHECKTABLE statement with valid REPAIR options is to start SQL Server normally and then explicitly set the database in single user mode. You can do this from either the Enterprise Manager or the Query Analyzer.

From Enterprise Manager:
  1. Right-click the database name, and then click Properties.
  2. In the Properties dialog box, click Options.
  3. Select the single user option, and then click OK.
From Query Analyzer:
Use mastergosp_dboption dbname, single, true				
After the database is in single user mode, you can then run the DBCC CHECKDB or DBCC CHECKTABLE statements with the valid REPAIR options.

If you are using SQL Server 2005

For more information about how to start SQL Server in single-user mode, visit the following Microsoft Developer Network (MSDN) Web site:
MORE INFORMATION
If you start SQL Server in single user mode (by using -m) and then you run a DBCC CHECK statement (CHECKDB or CHECKTABLE) with one of the valid REPAIR options, SQL Server generates this error message:
Repair statement not processed. Database needs to be in single user mode.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The preceding error message corresponds to error number 7919.

The following example illustrates the problem:
  1. Start SQL Server from a command prompt by using:sqlservr -c -m

  2. Make a connection to SQL Server by using the Query Analyzer and then run the following code:
    Use pubsgoDBCC CHECKTABLE(Authors, REPAIR_REBUILD )go					
These steps cause the error listed at the beginning of the "More Information" section to occur.

This behavior is by design. When you start SQL Server in single user mode, you do not explicitly set the status of each database in single user mode. You only do that for the Master database. However, the DBCC CHECK statement checks the status for that specific database on which it is being run and if it is not set to single user the error message occurs.
Properties

Article ID: 264154 - Last Review: 02/22/2007 22:31:04 - Revision: 4.3

Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Workgroup Edition

  • kbinfo KB264154
Feedback
r varAutoFirePV = 1; var varClickTracking = 1; var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write("
대한민국 - 한국어
España - Español
Paraguay - Español
Venezuela - Español
"https://c1.microsoft.com/c.gif?DI=4050&did=1&t="> var Route = "76500"; var Ctrl = ""; document.write(" appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?"> >>