asked
Suspected DB now Corrupted DB (repair)
Hi all, I came back from holiday and I got working on this issue; Bummer...it`s a stunning "welcome back to work kind of a deal"; Ok so, this database was in suspected mode at first; I did all the regular checks and features; Such as checkdb, try to repair the DB; Over 20 errors ware fixed by the checkdb and repair, after that the status changed from suspected, but I can see now it has corruption, I`m stuck with this few errors now; I haven`t used checkdb with data loss because naturally I like to avoid the data loss; Any suggestions are welcomed and thank you in advance for your time reading this; **ERRORS:** Msg 8913, Level 16, State 3, Line 1 Extent (1:5449480) is allocated to 'dbo.dmi_object_type, dmi_object_type_unique' and at least one other object. Msg 8913, Level 16, State 4, Line 1 Extent (1:5449480) is allocated to 'dbo.dmi_object_type' and at least one other object. Msg 2533, Level 16, State 1, Line 1 Table error: page (1:5449480) allocated to object ID 549576996, index ID 2, partition ID 72057594040352768, alloc unit ID 72057594044743680 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header. Msg 2533, Level 16, State 1, Line 1 Table error: page (1:5449481) allocated to object ID 549576996, index ID 2, partition ID 72057594040352768, alloc unit ID 72057594044743680 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header. Msg 2533, Level 16, State 1, Line 1 Table error: page (1:5449482) allocated to object ID 549576996, index ID 2, partition ID 72057594040352768, alloc unit ID 72057594044743680 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header. Msg 2533, Level 16, State 1, Line 1 Table error: page (1:5449483) allocated to object ID 549576996, index ID 2, partition ID 72057594040352768, alloc unit ID 72057594044743680 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header. Msg 2533, Level 16, State 1, Line 1 Table error: page (1:5449484) allocated to object ID 549576996, index ID 2, partition ID 72057594040352768, alloc unit ID 72057594044743680 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header. Msg 2533, Level 16, State 1, Line 1 Table error: page (1:5449485) allocated to object ID 549576996, index ID 2, partition ID 72057594040352768, alloc unit ID 72057594044743680 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header. Msg 2533, Level 16, State 1, Line 1 Table error: page (1:5449486) allocated to object ID 549576996, index ID 2, partition ID 72057594040352768, alloc unit ID 72057594044743680 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header. Msg 2533, Level 16, State 1, Line 1 Table error: page (1:5449487) allocated to object ID 549576996, index ID 2, partition ID 72057594040352768, alloc unit ID 72057594044743680 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header. Msg 8935, Level 16, State 1, Line 1 Table error: Object ID 549576996, index ID 2, partition ID 72057594040352768, alloc unit ID 72057594044743680 (type In-row data). The previous link (1:5449216) on page (1:5449223) does not match the previous page (1:5449480) that the parent (1:5436295), slot 141 expects for this page. Msg 8978, Level 16, State 1, Line 1 Table error: Object ID 549576996, index ID 2, partition ID 72057594040352768, alloc unit ID 72057594044743680 (type In-row data). Page (1:5449223) is missing a reference from previous page (1:5449216). Possible chain linkage problem. Msg 8980, Level 16, State 1, Line 1 Table error: Object ID 549576996, index ID 2, partition ID 72057594040352768, alloc unit ID 72057594044743680 (type In-row data). Index node page (1:5436295), slot 140 refers to child page (1:5449480) and previous child (1:5449481), but they were not encountered. Msg 8976, Level 16, State 1, Line 1 Table error: Object ID 549576996, index ID 2, partition ID 72057594040352768, alloc unit ID 72057594044743680 (type In-row data). Page (1:5449481) was not seen in the scan although its parent (1:5436295) and previous (1:5449216) refer to it. Check any previous errors.
ID of 2 indicates that they are non-clustered indexes. You can just rebuild them and that should remove the corruption. If that doesn't, try dropping the non-clustered index altogether and then re-create them.
Hi JohnM; first and most of all thank you for your answer, we already tried rebuilding but the corruption was still there; We could not start the services for our application, because of some content missing; How ever, today we decided to use the data loss, sense we understand there is no other way, we end up having no data loss we recovered the DB and system started working; Again thank you for your time and answer; I love and hate when something works/stops working automatically; I still don`t know what I had to recover manually;
I have seen that you haven't mention the backup restoration of current database. If it was database corruption and you don't want to take risk of loosing the data then the first option is to use backup restore. However there are chances that the backup is corrupted too, In such situation on can use [SQL Index Recovery][1] program for corrupted database recovery. [1]:
https://www.systoolsgroup.com/sql-recovery.html
Steps to fix SQL database from SUSPECT mode:
- Turn off the suspect flag on the database and set it to EMERGENCY
EXEC sp_resetstatus ‘db_name’; ALTER DATABASE db_name SET EMERGENCY
- Perform a consistency check on the master database
DBCC CHECKDB (‘database_name’)
- Bring the database into the Single User mode and roll back the previous transactions
ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
- Take a complete backup of the database Attempt the Database Repair allowing some data loss
DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)
- Bring the database into the Multi-User mode
ALTER DATABASE database_name SET MULTI_USER
Refresh the database server and verify the connectivity of database
Read this blog: https://www.stellarinfo.com/blog/recover-sql-database-from-suspect-mode/
Solutions to fix corrupted DB
As, ID 2 means non-clustered index then, drop and recreate the index is a better solution. Right-Click on the non-clustered index and select Script Index as DROP and CREATE to New Query Window. Verify that the Drop-Create service has resolved the issue. Run the command DBCC CHECKDB — run DBCC CHECKDB to ensure the database is back to normal
DBCC CHECKDB (‘DatabaseName’) with NO_INFOMSGS
21 People are following this question.
