October 16, 2015 at 5:35 am
Hi Guys,
I have moved some DB's from a physical server to a virtual server while the physical server is being upgraded. This morning when I checked to make sure all the jobs are running fine I saw that a backup job failed for one of the databases. The error was :
Executed as user: NT AUTHORITY\SYSTEM. BACKUP 'database 1' detected an error on page (1:1400911) in file 'D:\Data\database 1.mdf'. [SQLSTATE 42000] (Error 3043) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Then I ran a DBCC check db command and found the following errors:
Msg 8928, Level 16, State 1, Line 1
Msg 8939, Level 16, State 98, Line 1
Msg 8976, Level 16, State 1, Line 1
Msg 8978, Level 16, State 1, Line 1
Msg 8939, Level 16, State 98, Line 1
Msg 8978, Level 16, State 1, Line 1
The messages varies from data from one page cannot be referenced on another, data page cannot be processed, page was not seen in the scan although the parent was etc etc.
Does anyone maybe have any advise or an article that I can read to help me repair my data pages with no data loss? How did this happen?
Thanks guys
October 16, 2015 at 5:45 am
Please run the following and post the full and complete output.
DBCC CheckDB ('<Database name>') WITH NO_INFOMSGS
Do you have a clean backup and a full set of log backups since that clean full backup?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2015 at 6:25 am
Msg 8928, Level 16, State 1, Line 1
Object ID 53575229, index ID 9, partition ID , alloc unit ID (type In-row data): Page (1:1400911) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 53575229, index ID 9, partition ID , alloc unit ID (type In-row data), page (1:1400911). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 96602121 and -4.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 9, partition ID , alloc unit ID (type In-row data). Page (1:1400911) was not seen in the scan although its parent (1:688650) and previous (1:1400910) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 9, partition ID , alloc unit ID (type In-row data). Page (1:1400912) is missing a reference from previous page (1:1400911). Possible chain linkage problem.
Msg 8928, Level 16, State 1, Line 1
Object ID 53575229, index ID 9, partition ID , alloc unit ID (type In-row data): Page (1:1401483) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 53575229, index ID 9, partition ID , alloc unit ID (type In-row data), page (1:1401483). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 96602121 and -4.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 9, partition ID , alloc unit ID (type In-row data). Page (1:1401483) was not seen in the scan although its parent (1:660528) and previous (1:1401482) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 9, partition ID , alloc unit ID (type In-row data). Page (1:1401484) is missing a reference from previous page (1:1401483). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 8 consistency errors in table 'ep_ACTION_track_Archive_20150819' (object ID 53575229).
CHECKDB found 0 allocation errors and 8 consistency errors in database 'epTracking_db'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (epTracking_db).
I just took out the people's ID Numbers.
October 16, 2015 at 6:55 am
Please look in sys.indexes, identify which index has an ID of 9 on the table with an objectid of 53575229.
Drop that index and rerun the CheckDB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2015 at 7:44 am
I cant find and object with that id
October 16, 2015 at 7:52 am
select object_name, * from epTracking_db.sys.indexes where object_id = 53575229 and index_id = 9
The table's called 'ep_ACTION_track_Archive_20150819'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2015 at 10:20 am
Script out that index first so you can easily re-create it later if you need to. The index definition itself could still be OK.
That's actually a "good error", in that it's not in the clustered index (index #1), which is the table itself and thus far harder to correct.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 16, 2015 at 10:37 am
Or, instead of scripting, dropping, and then recreating the index, you can simply disable the index (which drops the storage but keeps the definition), and the just rebuild the index to recreate it.
ALTER INDEX [your_index] ON [your_table] DISABLE;
GO
-- Re-run the DBCC CHECKDB, then if everything's clean:
ALTER INDEX [your_index] ON [your_table] REBUILD;
GO
-Eddie
Eddie Wuerch
MCM: SQL
October 19, 2015 at 6:32 am
I found it thanks Gila.
It is just a log DB and this error has occurred before, so I just create a new db, and renamed the current one.
Will run that rebuild to see if it solves the error.
Thanks for the great advise guys.
October 19, 2015 at 6:33 am
If you're getting repeated corruptions, there's likely a problem with your IO subsystem. Do some investigation and see if you can identify the problem, before something important gets affected.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2015 at 5:11 am
Hi Gila,
I have deleted the index but I am still getting the error.
When I use select * from epTracking_dbOld.sys.indexes where object_id = 53575229 and index_id = 9 the record still shows up.
When I run drop index [indexActionTrack, sysname,>] on dbo.ep_ACTION_track it says that is does not exist or I don't have permissions, but I am sysadmin and owner of the db.
Please advise
October 21, 2015 at 5:30 am
CheckDB again please, and don't edit the output at all.
GilaMonster (10/16/2015)
DBCC CheckDB ('<Database name>') WITH NO_INFOMSGS
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2015 at 5:44 am
I did check it again, it gives the exact same errors.
October 21, 2015 at 5:52 am
Post them again please, just to be sure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2015 at 6:45 am
Msg 8928, Level 16, State 1, Line 1
Object ID 53575229, index ID 9, partition ID , alloc unit ID (type In-row data): Page (1:1400911) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 53575229, index ID 9, partition ID , alloc unit ID (type In-row data), page (1:1400911). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 63047689 and -4.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 9, partition ID , alloc unit ID (type In-row data). Page (1:1400911) was not seen in the scan although its parent (1:688650) and previous (1:1400910) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 9, partition ID , alloc unit ID (type In-row data). Page (1:1400912) is missing a reference from previous page (1:1400911). Possible chain linkage problem.
Msg 8928, Level 16, State 1, Line 1
Object ID 53575229, index ID 9, partition ID , alloc unit ID (type In-row data): Page (1:1401483) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 53575229, index ID 9, partition ID , alloc unit ID (type In-row data), page (1:1401483). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 63047689 and -4.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 9, partition ID , alloc unit ID (type In-row data). Page (1:1401483) was not seen in the scan although its parent (1:660528) and previous (1:1401482) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 53575229, index ID 9, partition ID , alloc unit ID (type In-row data). Page (1:1401484) is missing a reference from previous page (1:1401483). Possible chain linkage problem.
CHECKDB found 0 allocation errors and 8 consistency errors in table 'ep_ACTION_track_Archive_20150819' (object ID 53575229).
CHECKDB found 0 allocation errors and 8 consistency errors in database 'epTracking_dbOld'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (epTracking_dbOld).
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply