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.
DBCC CHECKTABLE (<table_name>, REPAIR_REBUILD) WITH ALL_ERRORMSGS.
To verify whether still consistency error exists, we ran below command.
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
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.
DBCC CHECKTABLE (<table_name>, REPAIR_REBUILD) WITH ALL_ERRORMSGS.
GO
To verify whether still consistency error exists, we ran below command.
DBCC CHECKTABLE ('dbo.Table_abc');
GO
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