How to check and repair MSSQL databases or tables

      Comments Off on How to check and repair MSSQL databases or tables

SQL_Server_2008
Sometime happens with all to need to repair or check the consistency of the Microsoft SQL database.

Maybe you find the following error message on run your quesry’s:

SQL Server detected a logical consistency-based I/O error: incorrect checksum

For resolve this error can use the following commnads.

1. Select  and check the  database

use MyDbName;
dbcc checkdb with data_purity;

In case if you receive some error in teh Database tables can you apply for each table the next sentence, of course first time need to set the database to SINGLE USER MODE.

ALTER DATABASE [MyDbName] SET SINGLE_USER WITH NO_WAIT

2. Repair the errored table:

use MyDbName;
dbcc checktable('myDbTableName', repair_allow_data_loss)

3. After repair do you need to check again the database, and set to MULTI USER MODE

ALTER DATABASE [MyDbName] SET MULTI_USER WITH NO_WAIT

For more information visit the following sites:

http://technet.microsoft.com/en-us/library/ms188796.aspx