Home > T-SQL > T-SQL Repair Database Inconsistencies

T-SQL Repair Database Inconsistencies

SQL Server databases sometimes get consistency issues, when this occurs there is a simple process to resolve, follow this giude to resolve these.

To check an SQL 2005 Database for errors, you need to run the following command:

DBCC CHECKDB(<database name>)

If you run a CheckDB command on an SQL 2005 database and you are presented with errors in the result, such as the below:

CHECKDB found 0 allocation errors and 17 consistency errors in database '<database name>'.

You will then need to repair the database, to do this you still use the CHECKDB command, however you need to switch the database into SINGLE_USER mode first. When you switch to this mode the query where you have ran the command from will be the only connection left open on the database. So all other external connections will be severed and no more will be allowed.

ALTER DATABASE <database name>
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

Now that the database is in single user mode, you will need to repair the database and you now have three options these are:

1. REPAIR_ALLOW_DATA_LOSS
2. REPAIR_FAST
3. REPAIR_REBUILD

On the database I was repairing the errors that were detected we not in important areas in the database, so the data could be sacrificed. So to do this run the following:

DBCC CHECKDB(<database name>, REPAIR_ALLOW_DATA_LOSS)

Now check to make sure that the repair was completed well and all the errors are gone. Then you will need to switch back to MULTI_USER mode:

ALTER DATABASE <database name>
SET MULTI_USER;

Now the database errors should be all repaired and the database be back up and running.

CHECKDB found 0 allocation errors and 17 consistency errors in database '<database name>'.
  1. Jones
    August 29th, 2013 at 01:41 | #1

    you saved me today…
    Thanks a lot.

    it works.

  1. No trackbacks yet.
*

%d bloggers like this: