DBCC error .. - SQL Server Forums

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

DBCC error ..

Author

Topic

zaty2405
Yak Posting Veteran

58 Posts

Posted - 2010-04-29 : 02:38:22

Hi,

We have the following error repeatedly written to the sql server log:

A possible database consistency problem has been detected on database 'master'.
Error: 7987, Severity: 22, State: 3
Stack Signature for the dump is 0x1A7F5E70
Using 'dbghelp.dll' version '4.0.5'...
ex_raise2: Exception raised, major=79, minor=87, severity=22, attempting to create symptom dump .

I execute DBCC Checkdb on Master database but No Error.

But when I execute DBCC CHECKDB on MSDB database , I encountered the following:

Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1819153526, index ID 0: Page (1:1566) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1819153526, index ID 0, page (1:1566), row 21. Test (VarColOffsets + (int)(sizeof (class VarColOffset) * nVarCols) <= (nextRec - pRec)) failed. Values are 41319 and 682.
DBCC results for 'msdb'.
.
.
.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'log_shipping_plan_history' (object ID 1819153526).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'msdb'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (msdb ).

I do not have any good backup for my MSDB database.

Please help.

Thank you

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-04-29 : 03:58:51

Please post the entire output of the following, leaving nothing out.


DBCC CHECKDB ('MSDB') WITH NO_INFOMSGS, ALL_ERRORMSGS

--
Gail Shaw
SQL Server MVP

zaty2405
Yak Posting Veteran

58 Posts

Posted - 2010-04-29 : 04:09:20

Hi Gail,

Here is the output.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1819153526, index ID 0: Page (1:1566) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1819153526, index ID 0, page (1:1566), row 21. Test (VarColOffsets + (int)(sizeof (class VarColOffset) * nVarCols) <= (nextRec - pRec)) failed. Values are 41319 and 682.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'log_shipping_plan_history' (object ID 1819153526).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'msdb'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (msdb ).

Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-04-29 : 06:12:32

Ok, since you have no backup, the only option is to repair. It's not always safe to repair MSDB, but in this case it'll only lose some of your log shipping history. (That may or may not be acceptable)

Stop SQL Agent, take MSDB to single user mode and run checkDB with the repair_allow_data_loss option. It will discard damaged data, specifically one page in the log_shipping_plan_history table.

Once it's fixed, sort out your backups. Not having backups of the system databases is unacceptable.

--
Gail Shaw
SQL Server MVP

zaty2405
Yak Posting Veteran

58 Posts

Posted - 2010-04-29 : 06:32:34

Hi Gail,

I cannot proceed with the repair because we cannot even access the database. It is mark Suspect. Executing sp_resetstatus also does not help.

Any idea?

Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-04-29 : 07:21:37

Without a backup, not many options left. What version of SQL are you running?

sp_resetstatus is only an option for a limited set of problems, and once you've fixed the cause.

--
Gail Shaw
SQL Server MVP

zaty2405
Yak Posting Veteran

58 Posts

Posted - 2010-04-29 : 07:28:59

Hi Gail,

This server is a secondary server for logshipping. We are running SQL 2000

We managed to resolve the issue by restoring from msdb database backup taken from the primary server.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-04-29 : 07:59:07

Not usually a good idea to copy system databases from one DB to another... Keep an eye open for future problems.

Now, go schedule some backups of the system databases and check through the event logs (windows), see if there's any sign of IO problems (that could have caused the corruption in the first place).

--
Gail Shaw
SQL Server MVP

zaty2405
Yak Posting Veteran

58 Posts

Posted - 2010-04-29 : 08:05:11

Yep, done that ... Thanks so much Gail :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources