4

I have a problem with my SQL Server database. Today I opened it and saw an error – SQL Server Table Corruption Error.

I don't know the reason for this error. I applied SQL Server Management Studio, but it couldn't completely restore my database.

Does someone know methods of repairing SQL Server databases?

4

Does the database open and you just can't view a single table, or are you unable to browse your database at all? Are other databases on the server affected, or just one? What's the current database status - offline, suspect, or something else? Each can mean different steps to correct or repair the issue.

Also, if you've got a backup handy, you'll want to keep it nearby in case you can't repair the corruption that caused this problem.

If you can open the database, to start you'll want to scan it with DBCC and see if anything obvious comes up:

DBCC CHECKDB ('YourDatabase') WITH NO_INFOMSGS

From there, deal with the error messages or warnings that you see - it should provide enough detail that you're able to search for the specific issues (or update your question to include those details so that we can help you more directly).

SqlMag has a great series on corruption, what it is, and how to respond to it, starting here:

http://sqlmag.com/blog/sql-server-database-corruption-part-i-what-corruption

Jump ahead to section 9 if you want to see their suggestions for fixing it, but be sure you understand what you're dealing with before you take any action.

1

Before taking any step, please read about the DBCC CHECKDB options in DBCC CHECKDB Use and Abuse by Richard Fryar. (I am not associated with the product promoted there).

  • To repair the table, change the database to single user mode (if it is in multi user mode)

    ALTER DATABASE [databasename] SET SINGLE_USER WITH NO_WAIT
    
  • To repair the table:

    use databasename;
    dbcc checktable('myDbTableName', repair_allow_data_loss)
    
  • After repair you need to check again the database, and set to multi user mode

    ALTER DATABASE [MyDbName] SET MULTI_USER WITH NO_WAIT​
    
0

In the case of database corruption, first, you should restore the database from the updated backup. If your backup is not updated then, you need to repair SQL database to get all the data. To repair corrupt SQL database, you need to find the exact reason for the corruption.

If it is clustered index then, you need to repair it. If it is related to non-clustered then, you can rebuild it.

If you are going to use DBCC CHECKDB Repair allow data loss then, you may or may not lose data.

Not the answer you're looking for? Browse other questions tagged or ask your own question.