ALTER DATABASE AdventureWorks2008R2 SET SINGLE_USER;
The above code would wait indefinitely if there is a lock on the database or users are connected to the database. So to overcome this situation, use the code below:
ALTER DATABASE AdventureWorks2008R2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
- If the AUTO_UPDATE_STATISTICS_AYSYNC option for the database is ON, then you will be unable to place the database in single-user mode because the background thread that is used to update the statistics takes a connection against the database.
- The DBCC offers two repair modes:
- REPAIR_REBUILD: This performs a repair that does not lead to any data loss.
- REPAIR_ALLOW_DATA_LOSS: This performs a repair and fixes to the corrupted database structures, and also results in data loss.
- The following example will let you understand the whole concept, where I have used DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS option;
ALTER DATABASE AdventureWorks2008R2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
BEGIN TRANSACTION;
DBCC CHECKDB ('AdventureWorks2008R2', REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE AdventureWorks2008R2 SET MULTI_USER;
The result can be seen as:
It’s the best practice if DBCC CHECKDB asks you to use REAPIR_ALLOW_DATA_LOSS, then take a full database backup first and then run DBCC CHECKDB with the repair option in a user transaction.
You can also repair it in Parallel, i.e. in a Multi-User. Since running DBCC CHECKDB in parallel is processor intensive. So you can disable it, if it is against your environment or situations with the help of trace flag 2528.
Well this was all about repairing database with DBCC CHECKDB.
And also comments on this!!
Reference: http://www.sqlservergeeks.com/repair-the-database-using-dbcc-checkdb/
