Failed to drop/rename a corrupted table in SQL Server

7vhp5slm  于 2023-08-02  发布在  SQL Server
关注(0)|答案(3)|浏览(117)

In my SQL, a table has corrupted. When renaming or removing it, I get this message:
Possible schema corruption. Run DBCC CHECKCATALOG. A severe error occurred on the current command. The results, if any, should be discarded.

I did tried DELETE , TRUNCATE and DROP but none of these works.

This corrupted table exist in every backup file. The data inside this table is not important, but the main point is to recreate it. How do I removing this corrupted/damaged table?

zengzsys

zengzsys1#

Please try the following in order to remove a corruption from a table:

  • To get the exact details about the table corruption, please execute below command:
Use YourDatabaseName
DBCC CHECKTABLE ('YourTableName')
Go
  • Now since you have the details about the corruption, in order to remove the corruption from a table, you need to DROP and RE-CREATE the PRIMARY KEY of that table as follows:
Use YourDatabaseName
Go
ALTER TABLE [YourTableName] DROP CONSTRAINT [Primary Key Constraint Name] 
GO
ALTER TABLE [YourTableName] ADD CONSTRAINT [Primary Key Constraint Name] Primary Key Clustered (Column(s))
GO
  • Now you can run the DBCC CHECKTABLE command again to confirm that the table corruption has been removed.

It indeed worked for me.

All the Best...!!!

rlcwz9us

rlcwz9us2#

http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-emergency-mode-repair-the-very-very-last-resort/
Hack the system tables to get the database into EMERGENCY mode. Use the undocumented and unsupported DBCC REBUILD_LOG command to build a new transaction log. Run DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option to fix up corruptions in the data files – both those that may have caused the issue, and those caused by rebuilding the transaction log (e.g. because an active transaction altering the database structure was lost). Figure out what data was lost or is transactionally inconsistent (e.g. because a transaction altering multiple tables was lost) as far as your business logic is concerned Take the database out of EMERGENCY mode And then all the other stuff like root-cause analysis and getting a better backup strategy

pgx2nnw8

pgx2nnw83#

If there is schema corruption in SQL server database you can try some some steps to remove it.

  1. Run DBCC CHECKCATALOG: This command will help to check logical and physical integrity of database. DBCC CHECKCATALOG('Name of database')
  2. TO identify table object ID: TO review the output for identifying object ID for corrupted table.
  3. Using sp_MSforeachtable for deleting the table EXEC sp_MSforeachtable 'IF OBJECT_ID(''?'') = YourObjectID EXEC(''DROP TABLE ?'')'
  4. Confirming on the table removal: Need to check if the table has been removed successfully from the database. SELECT *FROM sys.tables WHERE name = 'TableName'

By these steps you can remove the corrupted table from database. I hope this will help you!!

相关问题