DBCC CheckDB Error
Question
Answers
-
Good day forgiven,
There is a reason that people backup their database, files, operating system, disks and so on...
My guess is that if you did not backup your database then you do not backup your machine disks as well, which might lead you to much more serious issues in thee future then "just" losing your database. This is a good (bad) learning experience that you must learn from it!If you read this message, then please stop everything and shut down your machine! backup your disks and design a good backup plan for the future.
Back to the issue...
As this issue has potential to destroy your database, and since we have no information regarding the issue or ability to check it directly, I will not tell you what to do, but maybe my comments here can help you
If you had a full machine backup or even a VSS backup, there was a good chance to save your data, without using undocumented tools and/or complex work, which most likely cost a lot more then hire an Experienced DBA to maintain the database on a regular base.
*** DISCLAIMER: The information in this post should not be used on a production SQL Server system. Any problem, corruption, damage, or loss you cause by using the information presented here is entirely your own responsibility. Use at your own risk. Danger of death. ***
There are some built-in tools that can help deal with fixing the files in the binary level of the file (the 0/1 bits) like the "dbcc writepage", but these should NOT be used, without FULL understanding of the binary file (the ability to read and understand the file using binary editor for example). Moreover, once you use these options Microsoft remove their responsibility! There are some companies and individuals experts that focus on fixing corrupted databases, but these cost a lot and will never promise that they will succeed in fully restore the database (you pay for the time even if there is no success!).
In some cases the issue related to metadata, which mean that theoretically the data itself might still be in the database, and therefore it can be saved. Moreover, it might be related to data in the system tables and not in the database itself. The procedure to restore the data can be simple as 10 minutes or several weeks, depending on case to case. In other cases, we can restore the data itself from the log file or from indexes (for example if specific byte in the data file, which include specific int value, is corrupted and that specific value stored in index, we might be able get the data from the index and manually fix the database file). In some cases fixing a corrupted database might involved with manually alter hidden system tables. In some cases we can restore the data by comparing the data to old version and manually copy data.... In short.. I would not say that you are doomed and there are lot techniques to fix corrupted database :-)
** It is always good idea to fix the data in development environment and NEVER on production!
** Fixing the database usually best done as fast as you notice the issue. Once you start trying to fix the database, continue to work with the corrupted database, or even by shout down the server, you might have change something and make the issue worse. First step in most cases is backup your current situation.
** Paul S. Randal has great article regarding undocumented commends. These are advance articles! HIGHLY recommended for advance users.
>> "No catalog entry found for partition ID 72057595723776000 in database 7"
* You know which partition has a potential problem. You should focus on this, but first make sure that you backup all the other data.>> "The metadata is inconsistent"
* By the way, CHECKDB can't repair metadata corruptions as much as I remember from Paul Randal blogs.>> "This error can occur if a stored procedure references a dropped table"
Did you try to check if the issue is related to SP, and recreate it if so?** All tables, even if not partitioned, are tracked internally as partitions. You can track the relevant partition by query the sys.partitions:
select * from sys.partitions where partition_id = 72057595723776000 -- this is your id according to the error message GO
Track the relevant Object and focus on it
** examine the sys.dm_db_partition_stats in order to find the page and row-count information related to the corrupted partition. An expert might focus on restoring the data on that specific page.
** I do not recommend this! but in extrime cases and AFTER we restore/copy any data that can be read (both from log file and data file) and as last step you can execute DBCC CHECKDB with the parameter REPAIR_ALLOW_DATA_LOSS, which mean that you will probably loss data and the CHECKDB might delete much more then only the related corrupted pages! in one case that I manually succeed to repair a corrupted several specific rows in table, the CHECKDB REPAIR_ALLOW_DATA_LOSS deleted all the data on the related table!
do your best to save what’s left.
In any case, this is beyond the forum scope I think, since the official way is to restore using your last good backup :-)
I hope this gave some insights and maybe help youVery simple cases of corruptions and how to fix them without the use of backups
>> http://www.sqlskills.com/blogs/paul/using-the-dedicated-admin-connection-to-fix-msg-8992-corrupt-system-tables/
>> http://www.sqlskills.com/blogs/paul/disaster-recovery-101-fixing-metadata-corruption-without-a-backup/
Ronen Ariely
[Personal Site] [Blog] [Facebook] [Linkedin]- Edited by pituachMVP Monday, May 23, 2016 7:36 AM
- Proposed as answer by Ed Price - MSFTMicrosoft employee Monday, July 18, 2016 8:36 AM
- Marked as answer by Ed Price - MSFTMicrosoft employee Thursday, December 15, 2016 5:27 AM
All replies
-
Good day forgiven,
There is a reason that people backup their database, files, operating system, disks and so on...
My guess is that if you did not backup your database then you do not backup your machine disks as well, which might lead you to much more serious issues in thee future then "just" losing your database. This is a good (bad) learning experience that you must learn from it!If you read this message, then please stop everything and shut down your machine! backup your disks and design a good backup plan for the future.
Back to the issue...
As this issue has potential to destroy your database, and since we have no information regarding the issue or ability to check it directly, I will not tell you what to do, but maybe my comments here can help you
If you had a full machine backup or even a VSS backup, there was a good chance to save your data, without using undocumented tools and/or complex work, which most likely cost a lot more then hire an Experienced DBA to maintain the database on a regular base.
*** DISCLAIMER: The information in this post should not be used on a production SQL Server system. Any problem, corruption, damage, or loss you cause by using the information presented here is entirely your own responsibility. Use at your own risk. Danger of death. ***
There are some built-in tools that can help deal with fixing the files in the binary level of the file (the 0/1 bits) like the "dbcc writepage", but these should NOT be used, without FULL understanding of the binary file (the ability to read and understand the file using binary editor for example). Moreover, once you use these options Microsoft remove their responsibility! There are some companies and individuals experts that focus on fixing corrupted databases, but these cost a lot and will never promise that they will succeed in fully restore the database (you pay for the time even if there is no success!).
In some cases the issue related to metadata, which mean that theoretically the data itself might still be in the database, and therefore it can be saved. Moreover, it might be related to data in the system tables and not in the database itself. The procedure to restore the data can be simple as 10 minutes or several weeks, depending on case to case. In other cases, we can restore the data itself from the log file or from indexes (for example if specific byte in the data file, which include specific int value, is corrupted and that specific value stored in index, we might be able get the data from the index and manually fix the database file). In some cases fixing a corrupted database might involved with manually alter hidden system tables. In some cases we can restore the data by comparing the data to old version and manually copy data.... In short.. I would not say that you are doomed and there are lot techniques to fix corrupted database :-)
** It is always good idea to fix the data in development environment and NEVER on production!
** Fixing the database usually best done as fast as you notice the issue. Once you start trying to fix the database, continue to work with the corrupted database, or even by shout down the server, you might have change something and make the issue worse. First step in most cases is backup your current situation.
** Paul S. Randal has great article regarding undocumented commends. These are advance articles! HIGHLY recommended for advance users.
>> "No catalog entry found for partition ID 72057595723776000 in database 7"
* You know which partition has a potential problem. You should focus on this, but first make sure that you backup all the other data.>> "The metadata is inconsistent"
* By the way, CHECKDB can't repair metadata corruptions as much as I remember from Paul Randal blogs.>> "This error can occur if a stored procedure references a dropped table"
Did you try to check if the issue is related to SP, and recreate it if so?** All tables, even if not partitioned, are tracked internally as partitions. You can track the relevant partition by query the sys.partitions:
select * from sys.partitions where partition_id = 72057595723776000 -- this is your id according to the error message GO
Track the relevant Object and focus on it
** examine the sys.dm_db_partition_stats in order to find the page and row-count information related to the corrupted partition. An expert might focus on restoring the data on that specific page.
** I do not recommend this! but in extrime cases and AFTER we restore/copy any data that can be read (both from log file and data file) and as last step you can execute DBCC CHECKDB with the parameter REPAIR_ALLOW_DATA_LOSS, which mean that you will probably loss data and the CHECKDB might delete much more then only the related corrupted pages! in one case that I manually succeed to repair a corrupted several specific rows in table, the CHECKDB REPAIR_ALLOW_DATA_LOSS deleted all the data on the related table!
do your best to save what’s left.
In any case, this is beyond the forum scope I think, since the official way is to restore using your last good backup :-)
I hope this gave some insights and maybe help youVery simple cases of corruptions and how to fix them without the use of backups
>> http://www.sqlskills.com/blogs/paul/using-the-dedicated-admin-connection-to-fix-msg-8992-corrupt-system-tables/
>> http://www.sqlskills.com/blogs/paul/disaster-recovery-101-fixing-metadata-corruption-without-a-backup/
Ronen Ariely
[Personal Site] [Blog] [Facebook] [Linkedin]- Edited by pituachMVP Monday, May 23, 2016 7:36 AM
- Proposed as answer by Ed Price - MSFTMicrosoft employee Monday, July 18, 2016 8:36 AM
- Marked as answer by Ed Price - MSFTMicrosoft employee Thursday, December 15, 2016 5:27 AM
