October 14, 2009 at 7:31 am
I ran a DBCC(CHECKDB) on the MSDB Database and got the following error:
Msg 8928, Level 16, State 1, Line 1
Object ID 2069582411, index ID 0: Page (1:142338) could not be processed. See other errors for details.
Msg 8944, Level 16, State 16, Line 1
Table error: Object ID 2069582411, index ID 0, page (1:142338), row 18. Test (VarColOffsets + (int)(sizeof (class VarColOffset) * nVarCols) <= (nextRec - pRec)) failed. Values are 24635 and 836.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 2069582411, index ID 1. Page (1:142337) is missing a reference from previous page (1:142338). Possible chain linkage problem.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 2069582411, index ID 1. Page (1:142338) was not seen in the scan although its parent (1:180570) and previous (1:134126) refer to it. Check any previous errors.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'backupmediafamily' (object ID 2069582411).
CHECKDB found 0 allocation errors and 4 consistency errors in database 'msdb'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (msdb ).
Here is my solution and wanted opinions:
USE [master]
GO
ALTER DATABASE [msdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [msdb] SET SINGLE_USER
GO
DBCC CHECKDB (msdb, REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE [msdb] SET MULTI_USER
October 14, 2009 at 7:43 am
What about restoring from a backup?
Repair_allow _Data_loss does not mean there has to be data loss though so your solution was probably fine.
October 14, 2009 at 9:51 am
Seth Lynch (10/14/2009)
Repair_allow _Data_loss does not mean there has to be data loss though so your solution was probably fine.
The majority of the time it does. There are a few odd cases where it doesn't. In this case it does, one page (at least) of the actual data of the table identified by ObjectID=2069582411 will be deleted. (page 1:142338 specifically)
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 14, 2009 at 9:55 am
I'm sure you are right.
The only time I have used it was for this error:
Incorrect PFS free space information for page (1:105428) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL
Which didn't loose data - that I know of. But I think this was more of a bug than a data error
October 14, 2009 at 10:04 am
Seth Lynch (10/14/2009)
The only time I have used it was for this error:Incorrect PFS free space information for page (1:105428) in object ID 60, index ID 1, partition ID 281474980642816, alloc unit ID 71776119065149440 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL
Which didn't loose data - that I know of. But I think this was more of a bug than a data error
That is one of the very, very few cases where repair with allow_data_loss is required and no actual data loss occurs. In fact
http://sqlinthewild.co.za/index.php/2009/06/03/does-repair_allow_data_loss-cause-data-loss/
The blog post needs a couple corrections, but it's mostly right.
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 14, 2009 at 10:07 am
ericwenger1 (10/14/2009)
Here is my solution and wanted opinions:USE [master]
GO
ALTER DATABASE [msdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [msdb] SET SINGLE_USER
GO
DBCC CHECKDB (msdb, REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE [msdb] SET MULTI_USER
Repair, especially with the allow_data_loss options should never be the first thing attempted. It's a last resort when there's no other way of fixing the problem. Do you know how much data you've lost, from what table and do you know what effect that data loss will have? MSDB is where all the SQL Agent jobs, schedules and history are stored, as well as DTS packages, SSIS packages, backup histories, etc.
Take a look at this article. http://www.sqlservercentral.com/articles/65804/
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 14, 2009 at 5:14 pm
It's the backupmediafamily table in the MSDB. I started getting backup errors. We use redgate backups. Because the backups were failing(and I believe its just one of the databases on the server that is the root cause) I started doing sql server backups and they work fine. I thought what I would do is restore a copy of the MSDB database, call it something else, and run the repair option on it to see what my results would be. This problem is not a show stopper. Its just causing me trouble on the Redgate Backups. As I mentioned my current work around is doing sql server backups instead.
thanks for you suggestion and please provide me more. I read your article, and I agree I don't want to make things worse with a repair option, but, I still don't see away around it.
October 14, 2009 at 11:50 pm
ericwenger1 (10/14/2009)
thanks for you suggestion and please provide me more. I read your article, and I agree I don't want to make things worse with a repair option, but, I still don't see away around it.
Restore from a clean backup. (you do have backups of MSDB?)
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, 2009 at 8:49 am
yes I do have backups of msdb
October 16, 2009 at 9:06 am
Then find the latest backup that's not corrupt and restore that. Look in Books Online for the procedure for restoring MSDB. It's not quite the same as for user databases.
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, 2009 at 3:08 pm
You can simply restore msdb from a backup that you have made as long as no one is using it. That really means to just stop the SQL Server Agent process and restore the database and then start the Agent back up. Works like a champ.
One issue though is to remember to press your F5 key to refresh your view in EM or you may end up giving yourself an unnecessary heart attack.
See the following if you want/need it straight from Microsoft:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_2w1f.asp
MJ
October 17, 2009 at 6:58 am
Thanks!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply