Tuesday, April 8, 2014

SQL Server Error: (Msg 7919, Level 16, State 3, Line 1) Repair statement not processed. Database needs to be in single user mode.

SQL Server Error:

If you're trying to run check database integrity with command "DBCC CHECKDB"  with 
REPAIR_ALLOW_DATA_LOSS, you will receive below error.

USE master
go
DBCC CHECKDB(N'Database Name',REPAIR_ALLOW_DATA_LOSS)

Msg 7919, Level 16, State 3, Line 1
Repair statement not processed. Database needs to be in single user mode.


Resolution:

If the database is in MULTI_USER mode, SQL Server will not allow you to run DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option. However you can run the DBCC CHECKDB command with this option. In order to fix page error and run this command you need to set database in SINGLE_USER mode. 

You can below command to set the database into SINGLE_USER mode.

USE [master]
GO
ALTER DATABASE [Database Name] SET  SINGLE_USER
GO

Once your database is set into single user mode, you can run checkDB command with REPAIR_ALLOW_DATA_LOSS option. 

Note: If you are fixing this issue in production environment make sure to perform this activity in the maintenance window.

No comments:

Post a Comment