How to Restore if MSDB is Corrupt? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to Restore if MSDB is Corrupt?

Author

Topic

DaveD
Starting Member

8 Posts

Posted - 2010-02-16 : 11:30:05

Hi all,

My SQL 2000 SP4 server's power supply croaked last night. I ran DBCC CHECKDB on all databases and found, of 20 dbs, three have problems, including MSDB. One corrected itself and shows no errors now. [?!] Fortunately, I have complete backups taken 2 hours prior to the failure, but when I try to restore in Enterprise Manager, I get the following errors (multiple times):

Microsoft SQL-DMO(ODBC SQLState:HY000)
Error 823: I/O error (torn page) detected during read at offset 0x00000030058000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL\data\msdbdata.mdf'.

Microsoft SQL-DMO(ODBC SQLState:HY000)
Error 3624:
Location: recbase.cpp:1378
Expression: m_offBeginVar < m_SizeRec
SPID: 56
Process ID: 960

Then, the Restore dialog box appears, but "First backup to restore" is empty.

How do I restore MSDB and the other database? Should I unattach MSDB and copy the backup file over the corrupted file and then re-attach? Will this work if I'm getting the above errors? If this fails, can I rebuild MSDB without fear of affecting my other 20 databases?

I'm terrified that whatever I try will damage things further. Any help is GREATLY appreciated.

Dave

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 12:03:55

If the database(s) are running FULL Recovery Model then BEFORE doing anything take a final / "tail" Transaction Log backup.

Secondly, I would recommend restoring to a new, temporary, database first to prove that everything is OK - once you start restoring to the actual file then if anything goes wrong all bets are off

You have corruption in the file. Are you running a RAID disk system? If so even with a power cut there should be no such corruption, so if you are using RAID then the likihood is that you have other hardware problems - which you should, obviously, fix before using the system further!

You can restore the database with SQL commands, which may be easier if you cannot easily get the answers into the GUI system (and, IME, you can spend a lot of time fiddling with the various boxes in the GUI forms)

Syntax for the Restore is here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300

(Read my second example first! it shows how to find the names of the Logical Files. My First Example can then be used to set up the Restore, including any / all TLog backups you have available)

Save the script, once you have made it, then it will be easy to repeat (either to have another go, or to use on the actual database - whereas if you use the GUI Forms you have to repeat all the steps AND get them exactly the same as before!!)

Once you have done the Restore use

DBCC CHECKDB('MyDatabaseName') WITH NO_INFOMSGS

on the database (i.e. the TEMP database initially) to prove that it is OK.

If not, restore from an earlier backup (and, optionally, all subsequent Tlog backups)

DaveD
Starting Member

8 Posts

Posted - 2010-02-16 : 12:52:00

THANK YOU, KRISTEN!!!

Uh oh. I DO NOT have a backup of MSDB. My maintenance plan backs up my user DBs (full) at midnight and transaction logs every hour.

This is indeed a RAID system - two IDE drives in a RAID 1 arrangement. However, I don't think it's considered "hardware RAID," because it's a Promise controller built into the Intel motherboard. For what it's worth, I've taken power hits before and never 'noticed' any trouble. This is a big surprise. To repair the system, I pulled the drives and swapped them into an IDENTICAL server (but less RAM - I forgot to check). Otherwise, identical.

I've poked around and see no hardware problems at all. CHKDSK shows "0 KB in bad sectors."

This server IS still live and that database that "fixed itself" is now showing 4 consistency errors:

Server: Msg 8928, Level 16, State 1, Line 1
Object ID 70343365, index ID 0: Page (1:4886) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 70343365, index ID 0, page (1:4886). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 70343365, index ID 0: Page (1:4887) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 70343365, index ID 0, page (1:4887). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
DBCC results for 'MyBizDB'.

What options does this leave me? I'm guessing I have no choice but to rebuild MSDB now and then try to restore the affected user DBs? If I rebuild MSDB should the aforementioned errors go away? It won't affect my other DBs, will it (except that I must rebuild my maintenance plan)?

If I chicken out or blow things up, who do you recommend for paid SQL Server 2000 telephone support? I'm desperate.

Again, thank you!!!

Dave

ps. Here's DBCC CHECKDB for MSDB:

Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:76781). The PageId in the page header = (0:0).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2037582297, index ID 0: Page (1:76780) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 2037582297, index ID 0, page (1:76780). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2037582297, index ID 0: Page (1:76781) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2117582582, index ID 0: Page (1:21632) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 2117582582, index ID 0, page (1:21632), row 0. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 3117 and 334.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2117582582, index ID 0: Page (1:67673) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 2117582582, index ID 0, page (1:67673), row 0. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 3117 and 324.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2117582582, index ID 0: Page (1:79761) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 2117582582, index ID 0, page (1:79761). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2117582582, index ID 0: Page (1:80801) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 2117582582, index ID 0, page (1:80801). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2117582582, index ID 0: Page (1:85037) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 2117582582, index ID 0, page (1:85037). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2117582582, index ID 0: Page (1:85658) could not be processed. See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 2117582582, index ID 0, page (1:85658), row 0. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 3117 and 324.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2117582582, index ID 0: Page (1:99614) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 2117582582, index ID 0, page (1:99614). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 2117582582, index ID 0: Page (1:115446) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 2117582582, index ID 0, page (1:115446). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
DBCC results for 'msdb'.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
DBCC results for 'sysobjects'.
There are 421 rows in 8 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 248 rows in 11 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 3582 rows in 124 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 426 rows in 162 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 186 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 14 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 2661 rows in 17 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 12 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'sysjobschedules'.
There are 4 rows in 1 pages for object 'sysjobschedules'.
DBCC results for 'RTblIfaceMem'.
There are 1189 rows in 9 pages for object 'RTblIfaceMem'.
DBCC results for 'backupfile'.
There are 1397306 rows in 29385 pages for object 'backupfile'.
DBCC results for 'syscategories'.
There are 19 rows in 1 pages for object 'syscategories'.
DBCC results for 'systargetservers'.
There are 0 rows in 0 pages for object 'systargetservers'.
DBCC results for 'RTblWorkspaceItems'.
There are 0 rows in 0 pages for object 'RTblWorkspaceItems'.
DBCC results for 'restorehistory'.
There are 2 rows in 1 pages for object 'restorehistory'.
DBCC results for 'systargetservergroups'.
There are 0 rows in 0 pages for object 'systargetservergroups'.
DBCC results for 'RTblDatabaseVersion'.
There are 1 rows in 1 pages for object 'RTblDatabaseVersion'.
DBCC results for 'systargetservergroupmembers'.
There are 0 rows in 0 pages for object 'systargetservergroupmembers'.
DBCC results for 'sysalerts'.
There are 9 rows in 1 pages for object 'sysalerts'.
DBCC results for 'RTblDTSProps'.
There are 0 rows in 0 pages for object 'RTblDTSProps'.
DBCC results for 'RTblVersionAdminInfo'.
There are 2333 rows in 30 pages for object 'RTblVersionAdminInfo'.
DBCC results for 'restorefile'.
There are 4 rows in 1 pages for object 'restorefile'.
DBCC results for 'sysoperators'.
There are 0 rows in 0 pages for object 'sysoperators'.
DBCC results for 'sysnotifications'.
There are 0 rows in 0 pages for object 'sysnotifications'.
DBCC results for 'RTblParameterDef'.
There are 136 rows in 1 pages for object 'RTblParameterDef'.
DBCC results for 'restorefilegroup'.
There are 2 rows in 1 pages for object 'restorefilegroup'.
DBCC results for 'systaskids'.
There are 0 rows in 0 pages for object 'systaskids'.
DBCC results for 'syscachedcredentials'.
There are 0 rows in 0 pages for object 'syscachedcredentials'.
DBCC results for 'RTblIfaceHier'.
There are 3349 rows in 18 pages for object 'RTblIfaceHier'.
DBCC results for 'logmarkhistory'.
There are 0 rows in 0 pages for object 'logmarkhistory'.
DBCC results for 'RTblNamedObj'.
There are 2196 rows in 22 pages for object 'RTblNamedObj'.
DBCC results for 'sysdtscategories'.
There are 3 rows in 1 pages for object 'sysdtscategories'.
DBCC results for 'RTblClassExtension'.
There are 69 rows in 1 pages for object 'RTblClassExtension'.
DBCC results for 'sysdtspackages'.
There are 3 rows in 1 pages for object 'sysdtspackages'.
DBCC results for 'RTblTypeInfo'.
There are 0 rows in 0 pages for object 'RTblTypeInfo'.
DBCC results for 'sysdtspackagelog'.
There are 0 rows in 0 pages for object 'sysdtspackagelog'.
DBCC results for 'RTblScriptDefs'.
There are 0 rows in 0 pages for object 'RTblScriptDefs'.
DBCC results for 'RTblOLPProps'.
There are 0 rows in 0 pages for object 'RTblOLPProps'.
DBCC results for 'sysdtssteplog'.
There are 0 rows in 0 pages for object 'sysdtssteplog'.
DBCC results for 'RTblEnumerationDef'.
There are 0 rows in 0 pages for object 'RTblEnumerationDef'.
DBCC results for 'sysdtstasklog'.
There are 0 rows in 0 pages for object 'sysdtstasklog'.
DBCC results for 'RTblSumInfo'.
There are 0 rows in 0 pages for object 'RTblSumInfo'.
DBCC results for 'RTblMDSProps'.
There are 0 rows in 0 pages for object 'RTblMDSProps'.
DBCC results for 'RTblEnumerationValueDef'.
There are 0 rows in 0 pages for object 'RTblEnumerationValueDef'.
DBCC results for 'RTblUMLProps'.
There are 0 rows in 0 pages for object 'RTblUMLProps'.
DBCC results for 'sysdbmaintplans'.
There are 2 rows in 1 pages for object 'sysdbmaintplans'.
DBCC results for 'sysdbmaintplan_jobs'.
There are 4 rows in 1 pages for object 'sysdbmaintplan_jobs'.
DBCC results for 'RTblUMXProps'.
There are 0 rows in 0 pages for object 'RTblUMXProps'.
DBCC results for 'sysdbmaintplan_databases'.
There are 1 rows in 1 pages for object 'sysdbmaintplan_databases'.
DBCC results for 'RTblSIMProps'.
There are 0 rows in 0 pages for object 'RTblSIMProps'.
DBCC results for 'sysdbmaintplan_history'.
There are 1000 rows in 37 pages for object 'sysdbmaintplan_history'.
DBCC results for 'RTblGENProps'.
There are 0 rows in 0 pages for object 'RTblGENProps'.
DBCC results for 'RTblDTMProps'.
There are 0 rows in 0 pages for object 'RTblDTMProps'.
DBCC results for 'log_shipping_primaries'.
There are 0 rows in 0 pages for object 'log_shipping_primaries'.
DBCC results for 'log_shipping_secondaries'.
There are 0 rows in 0 pages for object 'log_shipping_secondaries'.
DBCC results for 'RTblDBMProps'.
There are 0 rows in 0 pages for object 'RTblDBMProps'.
DBCC results for 'RTblEQMProps'.
There are 0 rows in 0 pages for object 'RTblEQMProps'.
DBCC results for 'log_shipping_monitor'.
There are 0 rows in 0 pages for object 'log_shipping_monitor'.
DBCC results for 'mswebtasks'.
There are 0 rows in 0 pages for object 'mswebtasks'.
DBCC results for 'log_shipping_databases'.
There are 0 rows in 0 pages for object 'log_shipping_databases'.
DBCC results for 'log_shipping_plans'.
There are 0 rows in 0 pages for object 'log_shipping_plans'.
DBCC results for 'RTblVersions'.
There are 2333 rows in 27 pages for object 'RTblVersions'.
DBCC results for 'log_shipping_plan_databases'.
There are 0 rows in 0 pages for object 'log_shipping_plan_databases'.
DBCC results for 'log_shipping_plan_history'.
There are 0 rows in 0 pages for object 'log_shipping_plan_history'.
DBCC results for 'RTblDBXProps'.
There are 0 rows in 0 pages for object 'RTblDBXProps'.
DBCC results for 'RTblRelships'.
There are 6922 rows in 146 pages for object 'RTblRelships'.
DBCC results for 'RTblSites'.
There are 44 rows in 1 pages for object 'RTblSites'.
DBCC results for 'RTblProps'.
There are 393 rows in 3 pages for object 'RTblProps'.
DBCC results for 'RTblRelshipProps'.
There are 28 rows in 1 pages for object 'RTblRelshipProps'.
DBCC results for 'RTblPropDefs'.
There are 797 rows in 10 pages for object 'RTblPropDefs'.
DBCC results for 'RTblRelColDefs'.
There are 320 rows in 4 pages for object 'RTblRelColDefs'.
DBCC results for 'RTblIfaceDefs'.
There are 453 rows in 5 pages for object 'RTblIfaceDefs'.
DBCC results for 'backupmediaset'.
There are 1340150 rows in 13944 pages for object 'backupmediaset'.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'backupmediaset' (object ID 2037582297).
DBCC results for 'sqlagent_info'.
There are 0 rows in 0 pages for object 'sqlagent_info'.
DBCC results for 'RTblClassDefs'.
There are 537 rows in 5 pages for object 'RTblClassDefs'.
DBCC results for 'sysdownloadlist'.
There are 0 rows in 0 pages for object 'sysdownloadlist'.
DBCC results for 'backupmediafamily'.
There are 1340344 rows in 38576 pages for object 'backupmediafamily'.
DBCC results for 'sysjobhistory'.
There are 400 rows in 103 pages for object 'sysjobhistory'.
DBCC results for 'sysjobs'.
There are 4 rows in 1 pages for object 'sysjobs'.
DBCC results for 'RTblTFMProps'.
There are 0 rows in 0 pages for object 'RTblTFMProps'.
DBCC results for 'RTblRelshipDefs'.
There are 144 rows in 1 pages for object 'RTblRelshipDefs'.
DBCC results for 'backupset'.
There are 1340368 rows in 56305 pages for object 'backupset'.
CHECKDB found 0 allocation errors and 16 consistency errors in table 'backupset' (object ID 2117582582).
DBCC results for 'sysjobservers'.
There are 4 rows in 1 pages for object 'sysjobservers'.
DBCC results for 'RTblTypeLibs'.
There are 17 rows in 1 pages for object 'RTblTypeLibs'.
DBCC results for 'sysjobsteps'.
There are 4 rows in 1 pages for object 'sysjobsteps'.
CHECKDB found 0 allocation errors and 20 consistency errors in database 'msdb'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (msdb ).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 13:14:13

Hopefully Gail will be along in a minute - Gail is the expert on interpreting DBCC CHECKDB reports.

If you want to do some googling in the meantime the limit of my knowledge is that I think you can rebuild MSDB from scratch. That will lose you all your backup history (presumably not a big deal, you either still have the files, or you don't, and the timestamps on the files will tell you which-comes-first. Plus MSDB is toast anyway, so you can't use Point & Click to restore anything anyway ...

... but you will also lose all your maintenance plans and any scheduled jobs that you have. (I think that's IT, but I'm not 100% sure if there is anything else in MSDB)

DaveD
Starting Member

8 Posts

Posted - 2010-02-16 : 13:28:58

OH NO!!!!! Other databases that were good an hour ago are now showing inconsistencies too!!!

This is a live server, but I have no choice but to shut things down....

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 13:50:14

Notwithstanding that your RAID is not a "hardware RAID", I think it should have survived a power cut.

So maybe there is a hardware fault - Disk controller or RAM.

Have a look in Event Log and SQL Error log for anything unusual that might point at the culprit.

I've had a power cut take a RAID5 disk down - power surge, I suppose, particularly on systems that have been up, and running, for years without a break ...

DaveD
Starting Member

8 Posts

Posted - 2010-02-16 : 13:58:02

And then it STOPS?! Well, not quite.

I just restarted the SQL server and now all of the databases are clean - 0 inconsistencies, EXCEPT msdb. [?!?!] I have no clue what's going on here but am suddenly optimistic that maybe my databases aren't disintegrating?

Any help is SO GREATLY APPRECIATED!!!

Dave

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 14:04:56

More likely the Disk Controller was MISS-reading the data before, causing DBCC CHECKDB to THINK there was an error (when database was actually OK).

Now they have read OK ...

... next time they may read BADLY

If a disk block reads badly AND saves to disk (e.g. during an INSERT / UPDATE statement) then you will be in trouble ...

Restore the DBs to a temporary "stand-by" server for the time being - is that an option?

Kristen
Test

22859 Posts

Posted - 2010-02-16 : 14:06:25

P.S. Could, presumably, be intermittent RAM fault, rather than Controller fault ... or possibly something else interfering.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-02-16 : 16:40:20

You're not going to like this...

Restore from backup. If you don't have a backup, script out what you can and search MSDB (or Books Online) for the way to rebuild the MSDB database. CheckDB repair is not recommended on system databases.

I would suggest hardware diagnostics at this point, and backups taken and verified on another server.

--
Gail Shaw
SQL Server MVP

DaveD
Starting Member

8 Posts

Posted - 2010-02-16 : 20:00:04

Kristen, your instincts appear to have been right! I didn't want to believe that two identical servers could behave so differently but chose to swap back in the failed server (after replacing the PSU), just to be sure it wasn't a hardware issue before mucking with the data. I booted the old box up and ALL OF THE INCONSISTENCIES DISAPPEARED! EVEN MSDB CAME BACK WITHOUT SO MUCH AS A BLEMISH! [?!]

I'm perplexed but overjoyed! I'll run some more stringent tests on the replacement server's memory and other components later. Right now, I need sedatives and alcohol. And Olympics - watch somebody else beat themselves up for a while. Curl, you crazy freak, cuuuuuurl!!! ;-D

Thanks again!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-02-17 : 02:45:24

Excellent. Now schedule backups of the system DBs (master, model, msdb) so if something like this does happen again, you'll have the option of restoring.

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2010-02-17 : 02:46:16

Add DBCC CHECKDB to your maintenance routines (maybe it is already there) and make sure that you get the alerts. Assume that the COMMs may fail - so either you should always get the report - thus an Error Report or NO REPORT is an alert; or manually check the Logs each day

Backup your system databases. Next time your system is down for maintenance COPY the master / msdb / model files to a "safe copy". Repeat whenever you service pack the machine. Then at least you have a known-good copy of those databases to copy back, and then restore to, if you hit the buffers in the future.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources