May 21, 2010 at 12:58 pm
I've run DBCC CHECKDB ('DatabaseName') WITH NO_INFOMSGS on our database and got back 60 errors. Some examples of the errors are:
Msg 8904, Level 16, State 1, Line 1
Extent (1:20155592) in database ID 5 is allocated by more than one allocation object.
Msg 8913, Level 16, State 4, Line 1
Extent (1:20155592) is allocated to 'dbo.media_object, pk_media_object' and at least one other object.
Msg 8951 Level 16, State 1, Line 1
Table error: table 'drm' (ID 1319675749). Data row does not have a matching index row in the index 'IX_UNQ_CODE_APP_TYPE' (ID 5). Possible missing or invalid keys for the index row matching:
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:20155612) allocated to object ID 1371151930, index ID 1, partition ID 371334789595136, alloc unit ID 71865978874101760 (type LOB data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
It then says that the minimum repair level for the errors is 'repair_allow_data_loss'. I've researched some of these errors and can't find much information other than to run the repair and accept the data loss or restore from a backup.
I have a question regarding the restore. We have weekly full backups and daily differentials, but I don't know when this corruption occurred. So my question is, if for example, I were to restore yesterday's full backup to some other server, the run DBCC CHECKDB on it, would some of the errors be gone?
Are there any other options to mitigate data loss? Would a consultant possibly be able to come in and do some other tricks? This is our Production database that is up 24/7 and is around 200 GB in size. The DBCC CHECKDB took over 1 hour to run. It doesn't appear that our application that uses the database is experiencing any problems. What is the worst case if we simply ignore these errors?
June 17, 2010 at 2:32 am
I have a question regarding the restore. We have weekly full backups and daily differentials, but I don't know when this corruption occurred. So my question is, if for example, I were to restore yesterday's full backup to some other server, the run DBCC CHECKDB on it, would some of the errors be gone?
If the corruption is happened after the backup then you even you might not get even single error.
I think by looking at the errors you have to get your data from your old backups, or you may loose data.
Nag
------------------------------------------------
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
June 17, 2010 at 10:48 am
Thanks.
June 17, 2010 at 11:08 am
kevin77 (5/21/2010)
It then says that the minimum repair level for the errors is 'repair_allow_data_loss'. I've researched some of these errors and can't find much information other than to run the repair and accept the data loss or restore from a backup.
Yup, that's pretty much your options.
Take a look at this article. http://www.sqlservercentral.com/articles/65804/
I have a question regarding the restore. We have weekly full backups and daily differentials, but I don't know when this corruption occurred. So my question is, if for example, I were to restore yesterday's full backup to some other server, the run DBCC CHECKDB on it, would some of the errors be gone?
Maybe. Depends when the corruption occurred. If it occurred before yesterday's backup, a restore of yesterday's backup will still have all the errors
Are there any other options to mitigate data loss? Would a consultant possibly be able to come in and do some other tricks?
No and no. There's damage to the data file, it's not something that there's a trick to fixing. Either restore a clean backup or accept the downtime and data loss from recovery.
This is why you should be running checkDB regularly, so that you can catch the corruption very soon after it happens and so that there's always the option of restoring a clean backup
What is the worst case if we simply ignore these errors?
SQL encounters the corruption at some point while doing a rollback or recovery and marks the database suspect - unusable.
Do not ignore this. It's a severity 24 error (second highest you can have), it is a severe error that threatens the database's transactional and structural integrity. It's not something that you ignore because it's not convenient.
Can you post the full output from CheckDB? I prefer to see everything that's wrong when recommending a course of action.
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
June 17, 2010 at 11:19 am
Kevin,
Try to drop the index and Primary Key which is involved first (preferably on a backup copy of the database) and then retry the dbcc. It appears that the errors are index related and you may not lose any data.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
June 17, 2010 at 11:23 am
Thanks, Gail. The complete output from CheckDB is:
Msg 8904, Level 16, State 1, Line 1
Extent (1:20155592) in database ID 5 is allocated by more than one allocation object.
Msg 8904, Level 16, State 1, Line 1
Extent (1:20155600) in database ID 5 is allocated by more than one allocation object.
Msg 8904, Level 16, State 1, Line 1
Extent (1:20155608) in database ID 5 is allocated by more than one allocation object.
CHECKDB found 3 allocation errors and 0 consistency errors not associated with any single object.
Msg 8913, Level 16, State 4, Line 1
Extent (1:20155592) is allocated to 'dbo.media_object, pk_media_object' and at least one other object.
Msg 8913, Level 16, State 4, Line 1
Extent (1:20155600) is allocated to 'dbo.media_object, pk_media_object' and at least one other object.
Msg 8913, Level 16, State 3, Line 1
Extent (1:20155608) is allocated to 'dbo.media_object, pk_media_object' and at least one other object.
CHECKDB found 3 allocation errors and 0 consistency errors in table 'media_object' (object ID 91147370).
Msg 8951, Level 16, State 1, Line 1
Table error: table 'drm' (ID 1319675749). Data row does not have a matching index row in the index 'IX_UNQ_CODE_APP_TYPE' (ID 5). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 1
Data row (1:20142295:2) identified by (id = 89631) with index values 'code = 'T_MNY_597_1262293185000101933342' and app_name = 'AllSportGPS' and type = 'QPASS''.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'drm' (object ID 1319675749).
Msg 8952, Level 16, State 1, Line 1
Table error: table 'device_tracking' (ID 1367675920). Index row in index 'IX_device_tracking_device_name_version_manufacturer_model_carrier_firmware_user' (ID 2) does not match any data row. Possible extra or invalid keys for:
Msg 8956, Level 16, State 1, Line 1
Index row (1:21458102:5) with values (device_id = '2a23870ead6599a005dd7c101c1f73f49046fb66' and application_name = 'AllSportGPS' and application_version = '1.4.1' and device_manufacturer = '' and device_model = '' and device_carrier = '' and device_firmware = '' and user_id = 71652) pointing to the data row identified by (id = 30521).
Msg 8952, Level 16, State 1, Line 1
Table error: table 'device_tracking' (ID 1367675920). Index row in index 'IX_device_tracking_user' (ID 3) does not match any data row. Possible extra or invalid keys for:
Msg 8956, Level 16, State 1, Line 1
Index row (1:20402347:93) with values (user_id = 71652 and id = 30521) pointing to the data row identified by (id = 30521).
Msg 8952, Level 16, State 1, Line 1
Table error: table 'device_tracking' (ID 1367675920). Index row in index 'IX_device_tracking_name_version' (ID 11) does not match any data row. Possible extra or invalid keys for:
Msg 8956, Level 16, State 1, Line 1
Index row (1:20077865:72) with values (application_name = 'AllSportGPS' and application_version = '1.4.1' and id = 30521) pointing to the data row identified by (id = 30521).
CHECKDB found 0 allocation errors and 3 consistency errors in table 'device_tracking' (object ID 1367675920).
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:20155608) allocated to object ID 1371151930, index ID 1, partition ID 371334789595136, alloc unit ID 71865978874101760 (type LOB data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8913, Level 16, State 4, Line 1
Extent (1:20155608) is allocated to 'dbo.media_cache, PK__media_cache__52AE4273' and at least one other object.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:20155609) allocated to object ID 1371151930, index ID 1, partition ID 371334789595136, alloc unit ID 71865978874101760 (type LOB 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:20155610) allocated to object ID 1371151930, index ID 1, partition ID 371334789595136, alloc unit ID 71865978874101760 (type LOB 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:20155611) allocated to object ID 1371151930, index ID 1, partition ID 371334789595136, alloc unit ID 71865978874101760 (type LOB 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:20155612) allocated to object ID 1371151930, index ID 1, partition ID 371334789595136, alloc unit ID 71865978874101760 (type LOB 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:20155613) allocated to object ID 1371151930, index ID 1, partition ID 371334789595136, alloc unit ID 71865978874101760 (type LOB 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:20155614) allocated to object ID 1371151930, index ID 1, partition ID 371334789595136, alloc unit ID 71865978874101760 (type LOB 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:20155615) allocated to object ID 1371151930, index ID 1, partition ID 371334789595136, alloc unit ID 71865978874101760 (type LOB data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
CHECKDB found 1 allocation errors and 8 consistency errors in table 'media_cache' (object ID 1371151930).
Msg 8952, Level 16, State 1, Line 1
Table error: table 'Trip' (ID 1698105090). Index row in index 'IX_Trip@tripname' (ID 2) does not match any data row. Possible extra or invalid keys for:
Msg 8956, Level 16, State 1, Line 1
Index row (1:17717560:68) with values (tripname = 'Running 4/13/2010' and tripid = 679106) pointing to the data row identified by (tripid = 679106).
Msg 8952, Level 16, State 1, Line 1
Table error: table 'Trip' (ID 1698105090). Index row in index 'trip_fk_txtID_idx' (ID 25) does not match any data row. Possible extra or invalid keys for:
Msg 8956, Level 16, State 1, Line 1
Index row (1:20433885:400) with values (fk_txtid = 3226292 and tripid = 679106) pointing to the data row identified by (tripid = 679106).
Msg 8952, Level 16, State 1, Line 1
Table error: table 'Trip' (ID 1698105090). Index row in index 'trip_trailhead_idx' (ID 29) does not match any data row. Possible extra or invalid keys for:
Msg 8956, Level 16, State 1, Line 1
Index row (1:20128208:342) with values (trailhead = NULL and tripid = 679106) pointing to the data row identified by (tripid = 679106).
Msg 8952, Level 16, State 1, Line 1
Table error: table 'Trip' (ID 1698105090). Index row in index 'trip_base_trip_id_idx' (ID 30) does not match any data row. Possible extra or invalid keys for:
Msg 8956, Level 16, State 1, Line 1
Index row (1:20123608:457) with values (base_trip_id = NULL and tripid = 679106) pointing to the data row identified by (tripid = 679106).
CHECKDB found 0 allocation errors and 4 consistency errors in table 'Trip' (object ID 1698105090).
Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:20150094) in object ID 1707153127, index ID 1, partition ID 393354964041728, alloc unit ID 71887999048548352 (type LOB data). Expected value 100_PCT_FULL, actual value 80_PCT_FULL.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1707153127, index ID 1, partition ID 393354964041728, alloc unit ID 71887999048548352 (type LOB data). The off-row data node at page (1:20150094), slot 47, text ID 1057937555456 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1707153127, index ID 1, partition ID 393354964041728, alloc unit ID 71887999048548352 (type LOB data). The off-row data node at page (1:20150094), slot 48, text ID 1057937620992 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1707153127, index ID 1, partition ID 393354964041728, alloc unit ID 71887999048548352 (type LOB data). The off-row data node at page (1:20150094), slot 50, text ID 1057937752064 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1707153127, index ID 1, partition ID 393354964041728, alloc unit ID 71887999048548352 (type LOB data). The off-row data node at page (1:20150094), slot 52, text ID 1057937817600 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1707153127, index ID 1, partition ID 393354964041728, alloc unit ID 71887999048548352 (type LOB data). The off-row data node at page (1:20150094), slot 54, text ID 1057937883136 is not referenced.
Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 1707153127, index ID 1, partition ID 393354964041728, alloc unit ID 71887999048548352 (type LOB data). The off-row data node at page (1:20150094), slot 56, text ID 1057937948672 is not referenced.
CHECKDB found 0 allocation errors and 7 consistency errors in table 'geocacher_tracking' (object ID 1707153127).
Msg 8952, Level 16, State 1, Line 1
Table error: table 'track' (ID 2094630505). Index row in index 'track_followed_route_id_idx' (ID 6) does not match any data row. Possible extra or invalid keys for:
Msg 8956, Level 16, State 1, Line 1
Index row (1:20418782:232) with values (followed_route_id = NULL and id = 896024) pointing to the data row identified by (id = 896024).
CHECKDB found 0 allocation errors and 1 consistency errors in table 'track' (object ID 2094630505).
Msg 8981, Level 16, State 1, Line 1
Table error: Object ID 2110630562, index ID 1, partition ID 419797261221888, alloc unit ID 419797261221888 (type In-row data). The next pointer of (1:21843697) refers to page (1:20155587). Neither (1:20155587) nor its parent were encountered. Possible bad chain linkage.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:20155592) allocated to object ID 2110630562, index ID 1, partition ID 419797261221888, alloc unit ID 419797261221888 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8913, Level 16, State 3, Line 1
Extent (1:20155592) is allocated to 'dbo.track_point' and at least one other object.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:20155593) allocated to object ID 2110630562, index ID 1, partition ID 419797261221888, alloc unit ID 419797261221888 (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:20155594) allocated to object ID 2110630562, index ID 1, partition ID 419797261221888, alloc unit ID 419797261221888 (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:20155595) allocated to object ID 2110630562, index ID 1, partition ID 419797261221888, alloc unit ID 419797261221888 (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:20155596) allocated to object ID 2110630562, index ID 1, partition ID 419797261221888, alloc unit ID 419797261221888 (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:20155597) allocated to object ID 2110630562, index ID 1, partition ID 419797261221888, alloc unit ID 419797261221888 (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:20155598) allocated to object ID 2110630562, index ID 1, partition ID 419797261221888, alloc unit ID 419797261221888 (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:20155599) allocated to object ID 2110630562, index ID 1, partition ID 419797261221888, alloc unit ID 419797261221888 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8981, Level 16, State 1, Line 1
Table error: Object ID 2110630562, index ID 5, partition ID 1545697168064512, alloc unit ID 1545697168064512 (type In-row data). The next pointer of (1:21843621) refers to page (1:20155556). Neither (1:20155556) nor its parent were encountered. Possible bad chain linkage.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:20155600) allocated to object ID 2110630562, index ID 8, partition ID 72057594038779904, alloc unit ID 72057594042974208 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8913, Level 16, State 3, Line 1
Extent (1:20155600) is allocated to 'dbo.track_point, track_point_survey_source_idx' and at least one other object.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:20155601) allocated to object ID 2110630562, index ID 8, partition ID 72057594038779904, alloc unit ID 72057594042974208 (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:20155602) allocated to object ID 2110630562, index ID 8, partition ID 72057594038779904, alloc unit ID 72057594042974208 (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:20155603) allocated to object ID 2110630562, index ID 8, partition ID 72057594038779904, alloc unit ID 72057594042974208 (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:20155604) allocated to object ID 2110630562, index ID 8, partition ID 72057594038779904, alloc unit ID 72057594042974208 (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:20155605) allocated to object ID 2110630562, index ID 8, partition ID 72057594038779904, alloc unit ID 72057594042974208 (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:20155606) allocated to object ID 2110630562, index ID 8, partition ID 72057594038779904, alloc unit ID 72057594042974208 (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:20155607) allocated to object ID 2110630562, index ID 8, partition ID 72057594038779904, alloc unit ID 72057594042974208 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
CHECKDB found 2 allocation errors and 18 consistency errors in table 'track_point' (object ID 2110630562).
CHECKDB found 9 allocation errors and 42 consistency errors in database 'TrimbleOutdoors'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (TrimbleOutdoors).
I ran this over a month ago and have not done anything about it. As far as I know, the database and application are functioning properly. But I would like to try and fix the problem, even if it means data loss. So thank you for confirming that.
I do have one other question/comment about your recommendation for running CheckDB every day. My response would be that we can't. Our database is over 300 GB in size and attempting to run CheckDB on the production server causes so much disk utilization, that the application becomes completely unresponsive. Heck, you can't even open up Notepad on the server while CheckDB is running (or at least it takes about a minute to open).
This CheckDB was performed on a backup server from a recently restored database. If I recall, it took over an hour or two on a server that wasn't doing anything else.
But I'm happy to listen to any other recommendations you may have. I've read and followed a lot of your other advice on these forums.
June 17, 2010 at 11:25 am
Thanks, Steve. I did try dropping and rebuilding several indexes (just like you suggested, on a backup server using a recent restore of the database). But it didn't appear to fix any problems.
I may take another shot at it again though since you suggested it.
June 17, 2010 at 11:27 am
kevin77 (6/17/2010)
Thanks, Steve. I did try dropping and rebuilding several indexes (just like you suggested, on a backup server using a recent restore of the database). But it didn't appear to fix any problems.I may take another shot at it again though since you suggested it.
Don't bother, it won't fix most of the errors. Drop/create index will only fix corruption that's repairable with repair_rebuild.
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
June 17, 2010 at 11:31 am
OK. 🙂
June 17, 2010 at 11:36 am
kevin77 (6/17/2010)
I ran this over a month ago and have not done anything about it. As far as I know, the database and application are functioning properly. But I would like to try and fix the problem, even if it means data loss.
Well in that case restoring yesterday's backup's a waste of time. If the errors have been there for over a month, they're in every single backup since then.
The app may be OK now but you are running the risk of SQL encountering one of those corruptions while it's doing a roll forward/rollback and it marking your database as suspect - offline and unusable. You're essentially playing russian roulette with your DB and app. These errors should have been looked at when you found them, not a month later. It's now too late to do anything but repair and accept the data loss.
I do have one other question/comment about your recommendation for running CheckDB every day.
I said nothing about running it every day. I said
you should be running checkDB regularly,[snip] so that there's always the option of restoring a clean backup
How often that is is up to you and depends on your backup strategy and backup retention. Last place I worked at had a 1.2 TB database that took 6-8 hours to checkDB. We ran it every second week. since we retained full, diff and log backups for a month, that was an acceptable interval.
Btw, the 'restore backup elsewhere and checkDB that' is a good way of both testing your backups and checking your DB integrity and is fairly common on big 24x7 databases. You should be doing that often. How often? Up to you. Often enough that restore from backup is possible if the checkDB finds corruption.
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
June 17, 2010 at 12:35 pm
OK, thanks for all the help, Gail. We'll schedule some time to try and get this fixed.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply