Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Tuesday, April 7, 2020 5:35 AM
I find a corrupt table in my database.
ERROR: Possible schema corruption
an temporary table !
not important at all! really !
I tried dbcc checkdb and checktable with repair, but without success.
**
**
now, How the heeell can I delete the one corrupt table!
IT'S HURTING MY EYES!
Tuesday, April 7, 2020 5:57 AM
Can you show the entire error from DBCC CHECKDB?
It lookks very bad, BTW, do you have a good last backup? As this is may not repairable in any way. You're going to have to recreate the database from scratch.
Script all objects, most will likely fail because of the schema corruption
Export all data (bcp out). Some may well fail.
Recreate the database from scratch.
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
Tuesday, April 7, 2020 11:44 AM
thx, Can bcp export and import skip the bad table, and keep all datas (with attribute, views, keys and so on)?
Tuesday, April 7, 2020 1:13 PM
A drop table command should work, unless there is some exception. What error are you facing when dropping a corrupt table. And what do you mean it is temporary table ? Is it like #table_name
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
Tuesday, April 7, 2020 1:20 PM
Yep, if you know the name the bad table :-)
https://www.sqlservercentral.com/scripts/script-and-export-via-bcp-sql-tables
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
Tuesday, April 7, 2020 3:38 PM
Yep, if you know the name the bad table :-)
https://www.sqlservercentral.com/scripts/script-and-export-via-bcp-sql-tables
I'm sorry but that script won't fly. It is written for SQL Server 2000 and relies on xp_cmdshell and also sp_OACreate. Even if you wanted to try that out, you will notice that it won't work as it is because of interface changes.
BCP will not export schema. You need something else for that. URI's suggested that you use xp_cmdshell to execute BCP.EXE and then the COM-based DMO API through sp_OACreate to do the scripting. Since DMO, sp_OACreate are ancient, you don't want to touch that!
Use something current for the scripting. If you can use SSMS GUI, then just script the stuff, configuring the script wizard to your liking (right-click database, Generate Scripts). If you need to do that in code, then the functionality lives in the current SMO API (as i mention above DMO is ancient and dead). You can get to that code using SSIS or some other host language that can utilize .NET assemblies.
But follow Shanky's advice first: Try the DROP TABLE. Post the command you execute and the error messages you get.
Tibor Karaszi, SQL Server MVP (Web Blog)
Wednesday, April 8, 2020 6:37 AM
thanks
DBCC CHECKCATALOG: ok
DBCC CHECKDB & CHECKTABLE & DROP TABLE: error as below
Possible schema corruption. Run DBCC CHECKCATALOG.
Msg 211, Level 23, State 51, Line 3
Msg 0, Level 20, State 50, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Wednesday, April 8, 2020 9:25 AM
Hi m5d
1.Please refrence : possible-schema-corruption-run-dbcc-checkcatalog whether is helpful for you
2.Try code as:
Use master
Go
Alter database dbname set EMERGENCY
Alter database dbname single_user with rollback immediate
go
DBCC CHECKDB (dbname,'REPAIR_ALLOW_DATA_LOSS')
GO
Alter database dbname set muti_user
3.restore from your backup if you have one.
4.If the issue always exists after you have tested, please don't hesitate to ask professional engineers for help, and they will deal with your problem separately and confidentially:AssistedSupportProducts
Best Regards.
yuxi
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com
Wednesday, April 8, 2020 9:45 AM
Your database is so messed up so DBCC CHECKDB won't even run. That is bad.
You can go to sqlskills.com and see if Paul Randal has something specific on this. Don't have too much hopes, though.
So you are likely in for a restore from a healthy backup. Or exporting what can be salvaged and creating a new database, etc.
Of course, it is *possible* that emergency mode and repair allow data loss (as suggested elsewhere) works, but considering the severe corruption, I wouldn't have much hopes. Also be aware that this command just ignores all the data which doesn't seem healthy, so be prepared to lose an unknown amount of data.
Restore from a healthy backup is the recommended action.
Tibor Karaszi, SQL Server MVP (Web Blog)
Thursday, April 9, 2020 12:50 AM
Hi m5d,
Is the reply helpful?
Best Regards.
yuxi
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com
Thursday, April 9, 2020 4:59 AM
thanks to all, really grateful.
**drop table, checkdb, checktable with repair, nothing is working. and no error in Problem table.
**
same error as above.
now only hope is export as sql script ,and import back.
T_T
Thursday, April 9, 2020 6:34 AM
Hi m5d,
The last workaround is what you said, export and import.
However, in corrupt situations, if the system table or the system page corrupt, the only way is restore from the backup.
Hope the only hope you say will be helpful.
Best Regards.
yuxi
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com
Friday, April 10, 2020 1:05 AM
Hi m5d,
Is the reply helpful?
Do you export and import successfully?
Do you solve the issue?
Best Regards.
yuxi
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com
Tuesday, April 14, 2020 1:36 AM
Hi m5d,
Do you solve the issue?
Best Regards.
yuxi
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com
Wednesday, April 15, 2020 10:54 AM
Nope!
Export & Import as SQL file cause many problems, I don't have any time to fix all errors.
so the ultimate solution is: forget about it!
I feel better now.