Another Database Consistency Problem on MSDB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Another Database Consistency Problem on MSDB

Author

Topic

aashirwad
Starting Member

9 Posts

Posted - 2008-07-25 : 11:47:48

Friends,

This is a SQL Server 2005 database and I get this error when I run DBCC CHECKDB(msdb) WITH ALL_ERRORMSGS, NO_INFOMSGS

Msg 8967, Level 16, State 216, Line 1
An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

The following error messages shows up in SQL Server Error Log:

DBCC encountered a page with an LSN greater than the current end of log LSN (1113334:0:1) for its internal database snapshot. Could not read page (0:4072), database 'msdb' (database ID 31), LSN = (6684768:0:0), type = 78, isInSparseFile = 0. Please re-run this DBCC command.

DBCC CHECKDB (msdb) WITH all_errormsgs, no_infomsgs executed by sa terminated abnormally due to error state 1. Elapsed time: 0 hours 0 minutes 3 seconds.

My SQL Server 2005 is ProductVersion:9.00.3042.00 / SP2 / Standard Edition

Please Help!!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-25 : 12:11:28

Do you have a backup of MSDB?

--
Gail Shaw
SQL Server MVP

aashirwad
Starting Member

9 Posts

Posted - 2008-07-25 : 14:21:37

Yes... I have a backup. Shall I go ahead and import it?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-25 : 14:29:09

Also check TempDB space? SQL server uses TEMPDB while you run CHECKDB.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-26 : 11:06:16

Best way to recover from corruption is to restore a backup. Be careful, restoring MSDB isn't done the same way as the user databases. Google should get you a link on how.

CheckDB model and master, while you're at it. It doesn't hurt to be careful, especially with corruption.

--
Gail Shaw
SQL Server MVP

aashirwad
Starting Member

9 Posts

Posted - 2008-08-12 : 17:49:20

Imported from a backup file which was created in April '08. But still the problem persists. This is the error what I see even after importing. Please help. Thanks!

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x1; actual: 0xc644a250). It occurred during a read of page (1:52616) in database ID 4 at offset 0x00000019b10000 in file 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDB_Data.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. (Microsoft SQL Server, Error: 824)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=824&LinkId=20476

After doing DBCC CHECKDB(MSDB), I get the following messages:

Msg 8967, Level 16, State 216, Line 1
An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services.
DBCC results for 'msdb'.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'msdb'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-12 : 17:51:55

Restore an older backup, keep doing it until you've found one without corruption. This is why it's important to check the integrity of your databases on a fairly frequent schedule such as daily or weekly.

Otherwise, reinstall so that you get uncorrupted system databases and then restore your user databases.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2008-09-26 : 19:07:21

Check this out; http://www.sqlskills.com/blogs/paul/2008/05/22/CHECKDBBugThatPeopleAreHittingMsg8967Level16State216.aspx

JamesKerr
Starting Member

1 Post

Posted - 2009-03-25 : 11:36:27

I have experienced this issue twice in the past 3 weeks, with a different table being the culprit each time.

The first occurance on March 5th involved table msdb.dbo.backupfilegroup (this occured during a Reindex job)

The second occurance on March 21st involved table msdb.dbo.backupmediafamily (this also occured during a Reindex job)

My resolution was fairly simple:


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

I was able to complete this fix in about 2-5 minutes while running on production without causing any type of outage ... but my real issue is WHY is this corruption occuring.

If I get a good reason, I will post.

Do or do not . . . there is no TRY! --Yoda

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources