Checking / repairing a database or table for consistency errors / corruption with MSSQL

If you encounter consistency errors such as:

The Database ID 5, Page (1:4835927), slot 7 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.

We should firstly find identify the database ID 5 by running:

SELECT DB_NAME(2) AS Database_Name;

(where '5' is the database ID in question.)

DBCC CHECKDB ('MYDATABASE') WITH ALL_ERRORMSGS,NO_INFOMSGS

We can also check an induvidual table with:

USE MYDATABASE
DBCC CHECKTABLE ('YourTable'); WITH ALL_ERRORMSGS,NO_INFOMSGS

If you are not lucky enough to have an available backup to hand you can attempt to repair the databaser with the DBCC CHECKDB command.

Although before we perform this you should ensure that the database is put into single user mode - along with the 'ROLLBACK IMMEDIATE' switch that will rollback any user transactions immidiately so that the DB can drop into single user mode (i.e. all user connections have disconnected) or the 'WITH ROLLBACK AFTER 30' option which allows the users a number of seconds (30 in this case) to complete the transactions.

ALTER DATABASE MYDATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
BEGIN TRANSACTION;

or

ALTER DATABASE MYDATABASE SET SINGLE_USER WITH ROLLBACK AFTER 30;
BEGIN TRANSACTION;

Now there are two repair levels: 'REPAIR_REBUILD' which is used when the corruption is confined to non-clustered indexes which are redeundent and hence cause no data loss and 'REPAIR_ALLOW_DATA_LOSS' which can potentially cause data loss.

To repair the a single table we can use:

USE MYDATABASE
DBCC CHECKTABLE ('MYTABLE', REPAIR_ALLOW_DATA_LOSS);

or to attempt to repair the whole database:

DBCC CHECKDB ('MYDATABASE', REPAIR_ALLOW_DATA_LOSS);

And finally change the database back to multi-user mode with:

ALTER DATABASE MYDATABASE SET MULTI_USER;

BEGIN TRANSACTION