How to repair table SQL Server?

by
How to repair table SQL Server

SQL Server provides several methods for detecting and repairing damaged tables. Table corruption can occur due to a variety of reasons such as disk failures, software bugs, or user mistakes. When SQL Server encounters a damaged table, queries accessing that table may fail or produce unexpected results. Therefore, it is important to be able to identify and repair corrupted tables.

How to Check for Table Corruption

There are a few ways to check if a SQL Server table is corrupted:

  • Check DBCC CHECKDB results – DBCC CHECKDB will report any corruption found in databases and tables.
  • Examine error logs – Errors related to corrupt tables may be logged.
  • Query sys.dm_db_index_physical_stats – This DMV provides index fragmentation statistics that can indicate corruption.
  • Use DBCC CHECKTABLE – To check a specific table for corruption.

DBCC CHECKDB and DBCC CHECKTABLE are the two primary methods for detecting table corruption in SQL Server.

To check a specific table for corruption with DBCC CHECKTABLE:

DBCC CHECKTABLE (database_name, 'table_name')

This will report any corruption found in the specified table.

Repairing Corrupted Tables

If corruption is identified, the following methods can be used to repair damaged tables in SQL Server:

DBCC CHECKDB WITH REPAIR_REBUILD

DBCC CHECKDB has the ability to repair found corruption with its REPAIR_REBUILD option:

DBCC CHECKDB (database_name, REPAIR_REBUILD)

This will rebuild corrupted indexes and tables within the database. However, there are some limitations:

  • Cannot repair very severe corruption errors.
  • Requires sufficient free space to rebuild tables and indexes.
  • Can only rebuild tables, not restore lost data.

DBCC CHECKTABLE WITH REPAIR_REBUILD

To repair corruption on a specific table, use DBCC CHECKTABLE with the REPAIR_REBUILD option:

DBCC CHECKTABLE (database_name, 'table_name', REPAIR_REBUILD)

This works the same as CHECKDB, but is limited to a single table.

RESTORE TABLE FROM BACKUP

One method to recover a corrupt table is to restore it from a backup:

RESTORE TABLE database_name.schema.table_name  
   FROM database_backup_file;

Backups allow you to restore a table to an uncorrupted state. But this requires having a usable backup and only restores table data up to the point of the backup.

DROP AND RE-CREATE TABLE

Dropping the damaged table and re-creating it is a simple option if the table schema is still available:

DROP TABLE corrupted_table;

CREATE TABLE corrupted_table 
(
   -- Specify table schema here
);

This can easily rebuild the table, but any data will be lost unless it is restored from a backup.

Repair Examples

Here are some examples demonstrating how to repair corrupted tables in SQL Server using the different methods:

Using DBCC CHECKDB and CHECKTABLE

Check a table for corruption with DBCC CHECKTABLE:

DBCC CHECKTABLE (mydb, 'customers');

Output:

Msg 8976, Level 16, State 1: Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type In-row data), page (1:42448) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'customers' (object ID 0).
CHECKTABLE will fix the consistency errors.
Repair: Page (1:42448) has been deallocated
CHECKTABLE found 0 allocation errors and 0 consistency errors in table 'customers' (object ID 0).

This shows corruption in the customers table. To rebuild and repair it: 

DBCC CHECKTABLE (mydb, 'customers', REPAIR_REBUILD);

Restoring a Table from Backup

To restore a single corrupt table from backup:

RESTORE TABLE mydb.dbo.customers  
  FROM customers_backup
  WITH RECOVERY;

Dropping and Re-Creating a Table

The table schema is retrieved and then the table is rebuilt:

-- Get table schema
SELECT * 
INTO table_schema
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'corrupt_table';

-- Drop corrupt table
DROP TABLE corrupt_table;

-- Recreate table from schema
SELECT * 
INTO corrupt_table
FROM table_schema;

This demonstrates the general process of dropping and re-creating a corrupt table in SQL Server. The key steps are retrieving the table schema, dropping the damaged table, and re-creating it from the schema.

Causes of Table Corruption

Some potential causes of table corruption include:

  • Disk failures – Disk errors can cause data corruption.
  • Database page torn writes – Partial page writes can corrupt tables.
  • Software bugs – Bugs in SQL Server can introduce corruption.
  • Inadequate memory – Insufficient buffer pool memory can cause corruptions when data is paged out.
  • Sudden shutdowns – Instant power loss can result in torn writes.
  • Invalid entries – Users or applications inserting bad data into tables.
  • Wrongly issued DBCC commands – Some DBCC commands can corrupt tables if not used properly.

Identifying the root cause of corruption can help prevent future occurrences. Reviewing system logs, error messages, and application changes may reveal the cause.

Preventing Table Corruption

While some table corruption is unavoidable, the following practices can help reduce the chances of occurring:

  • Use recommended hardware – Enterprise storage, ECC memory, UPS power source.
  • Enable database page verify option – Helps detect torn page writes (SQL Server Enterprise Edition only).
  • Monitor disk health – Replace failing drives promptly.
  • Ensure sufficient memory – Avoid undersized buffer pools paging data out.
  • Implement DBCC CHECKDB regularly – Run CHECKDB weekly or monthly to detect issues early.
  • Protect against sudden shutdowns – Use automatically starting services, applications should handle shutdowns gracefully.
  • Follow best practices – Such as proper indexing, constraints, data types.
  • Test applications thoroughly – Fix any bugs that could introduce bad data.
  • Restrict user permissions – Don’t allow users to directly modify tables and potentially corrupt data.
  • Patch SQL Server regularly – To avoid any bugs that could cause corruption.

While table corruption cannot be fully prevented, following SQL Server best practices and monitoring for corruption can help avoid and detect issues.

Third Party Repair Tools

In addition to the built-in options, third party tools are available that provide expanded ability to detect and repair SQL Server corruption:

  • ApexSQL Recover – Repairs various database objects including tables.
  • Red-Gate SQL Backup – Can restore individual database objects.
  • Stellar Repair for SQL – Repairs entire SQL Server instances and databases.
  • Lepide SQL Storage Manager – Manages and restores SQL database objects.

These tools can provide more detailed diagnostics and expanded repair abilities beyond the native options. They are worth considering for mission critical SQL Server instances or particularly complex corruptions.

Conclusion

SQL Server provides CHECKDB and CHECKTABLE to detect table corruption, as well as options like REPAIR_REBUILD and RESTORE to fix issues. Preventing corruption through best practices and regular CHECKDBs can help avoid problems. In complex cases third party tools may assist the repair process. But in most situations the built-in DBCC commands can successfully troubleshoot and repair corrupted tables in SQL Server databases. Careful monitoring and maintenance are key to identifying and resolving corruption issues before they significantly impact applications and users.