DBCCCHECKDB usage manual repair Database

Quick Fix DBCCCHECKDB (Database Name, REPAIR_FAST) re-indexing and repair DBCCCHECKDB (Database Name, REPAIR_REBUILD) if necessary allow loss of data repair DBCCCHECKDB (Database Name, REPAIR_ALLOW_DATA_LOSS) if an error occurs: the repair statement is not processed. The database must be in single-user mode.

Quick Fix dbcc checkdb ('database name', REPAIR_FAST) re-indexing and repair dbcc checkdb ('database name', REPAIR_REBUILD) if necessary, allow the loss of data to repair dbcc checkdb ('database name', REPAIR_ALLOW_DATA_LOSS). If an error occurs, the repair statement is not processed. The database must be in single-user mode.

Quick Fix
Dbcc checkdb ('database name', REPAIR_FAST)
Re-indexing and repair
Dbcc checkdb ('database name', REPAIR_REBUILD)
If necessary, allow restoration of lost data
Dbcc checkdb ('database name', REPAIR_ALLOW_DATA_LOSS)

If an error occurs: the repair statement is not processed. The database must be in single-user mode.

You can enable the single-user mode to execute the stored procedure as follows:

Use master
Go
Sp_dboption Database Name, single, true

-- Change to a single user
Alter database ams2 set single_user with rollback immediate

-- Restore the database to multi-user mode
Alter database ams2 set multi_user with rollback immediate

######################################## ####################
######################################## ####################

Manual database repair example

Procedure:

Bytes ----------------------------------------------------------------------------------------------
Go to the SQL query analyzer and run the following statement:

-- Check database integrity
Dbcc checkdb ('ams1 ')

Execution result:
---------------------------------------------------------------
CHECKDB found 0 allocation errors and 11 consistency errors (in database 'ams1 ).
Repair_allow_data_loss is the lowest repair level (for errors found by dbcc checkdb (ams1 ).
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

It indicates that the database is faulty. If there are 11 errors, locate the error:
-------------------------------------------------------------------------------
The object 'tb _ Archives_File_1 'has 3777 rows on page 172.
CHECKDB finds 0 allocation errors and 2 consistency errors (in the 'tb _ Archives_File_1 'table, the Object ID of this table is 907150277 ).

It indicates that the 'tb _ Archives_File_1 'table does have two errors. It is no wonder that a query will crash, so run the statement to fix the table:
Bytes --------------------------------------------------------------------------------------
-- Restore a table at the repair_allow_data_loss level
Dbcc checktable ('tb _ Archives_File_1 ', repair_allow_data_loss)
Go

Execution result:
Server: Message 7919, level 16, status 3, Row 2
The repair statement is not processed. The database must be in single-user mode.
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.
Bytes ---------------------------------------------------------------------------------------------------
You need to change the database to "single-user mode", and then execute:
-- Change to a single user
Alter database ams2 set single_user with rollback immediate
Go
-- Table repaired at repair_allow_data_loss level
Dbcc checktable ('tb _ Archives_File_1 ', repair_allow_data_loss)
Go


-- If any problem persists, fix the index table.
Dbcc dbreindex ('tb _ Archives_File_1 ')

-- Restore the table
Dbcc checktable ('tb _ Archives_File_1 ')

Until no error is returned!

-- Check whether the query is normal
Select * from Tb_Archives_File_1

Query the error table again. No error is reported, and the data is intact ....

-- Restore the database to multi-user mode
Alter database ams2 set multi_user with rollback immediate