Tuesday, February 14, 2017

MSSQL: CheckDB failed for large user database which is of 2 TB

On one of the weekend, I received call from our team member who was in support that CheckDB maintenance job got failed on a large user database whose size was around 2 TB.

We have identified the table 'Table_abc' on which there is consistency issue from the SQL Error log based on the object_id. Table which got corrupted is of 50 GB. We ran below command to see if it fixes the issue.


To verify whether still consistency error exists, we ran below command.

DBCC CHECKTABLE ('dbo.Table_abc');    

But it was of no use still there was consistency error.

We created a new table 'Table_abc_new' and imported data into it using Export and Import wizard (BCP option also can be used).

Later created indexes and constraints similar to the old table 'Table_abc' and ran DBCC CHECKTABLE on the newly created table and there was no consistency issue.

Renamed the table 'Table_abc' to 'Table_abc_Old' and also renamed Table_abc_new' to 'Table_abc'.

Post Application team confirmation and after appropriate cooling period, dropped 'Table_abc_old' from the database.

You may refer below links for more information about CHECKDB and Repair options:
1. https://www.sqlskills.com/blogs/paul/misconceptions-around-database-repair/
2. https://msdn.microsoft.com/en-us/library/ms176064.aspx

No comments:

Post a Comment