Solved

msdb consistency errors

Posted on 2004-10-04
13
778 Views
Last Modified: 2008-01-09
We had some hard disk issues a while back and long story short we managed to mostly recover; however, whenever we run a backup of the db I get a torn page error in msdb.  When I run dbcc checkdb it finds that it has 16 consistency errors.  Problem is that while I was running regular backups of our "important" database, I didn't run any backups of msdb (still not completely sure what it is other than it has something to do with jobs).

I'd like to find out what my options are.... running checkdb with repair_rebuild or some other fancy checkdb thing or I read something about instmsdb.sql or something...?  
0
Comment
Question by:fisc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
13 Comments
 
LVL 10

Expert Comment

by:AustinSeven
ID: 12217251
The standard solutions like DBCC checkdb with the repair options (see BOL) will most likely result in data loss and would, therefore, still leave you with a problem.   The safe and sure fix would be to re-install SQL Server.  This isn't as bad as it sounds.  Here is a plan...

1. Script out the database users and make a note of all the Server Logins together with their settings
eg. default db's, language, fixed server roles and so on.

2. Save any DTS packages to disk

3. Script out any Jobs

4. backup application databases

5. detach application databases - must do this to ensure that you can re-attach

eg. exec sp_detach_db 'dbname'

6. uninstall SQL Server + reboot

7. re-install SQL Server and re-apply SP3a (plus any hotfixes you might have) + reboot

8. setup Logins and Users + re-test connectivity from apps.

9. re-apply DTS packages and Jobs

The above will fix the corrupt MSDB db.   If you feel nervous about this plan, try it against a test server or even a SQL Server installation on your desktop PC.  

AustinSeven
 
0
 
LVL 10

Expert Comment

by:AustinSeven
ID: 12217280
whoops... missed out a step between 7 & 8....

Re-attach application databases.  eg...

EXEC sp_attach_db @dbname = N'pubs',
   @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
   @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'


AustinSeven
0
 

Author Comment

by:fisc
ID: 12217724
That solution actually sounds REALLY scary.  Why is it that msdb is even that important?  We seem to be running just fine even with that db being corrupted.  If it just stores our database jobs and history of them, does it really even matter? Can I just somehow restore a clean msdb?  
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 10

Expert Comment

by:AustinSeven
ID: 12217877
Yes, you could restore from a backup of the MSDB database that was taken before the problem surfaced.  Do you have such a backup?  Sorry, I didn't point out that option.   I assumed you didn't have the backup because you would have restored it - end of problem.

It's not as scary as it sounds.  As I said, you can test it out first on a non-production server.   To be honest, as a DBA, you should be able to do that kind of thing because, one day, you might be forced into doing it in an emergency situation.

MSDB is a system database that stores all the DTS packages, Jobs and schedules.   It is important and although your system is 'working', it's definitely worth fixing.    Apart from problems that might crop up in the future, you might actually have some problems being caused through the corrupt msdb database that you aren't aware of yet.

AustinSeven
0
 

Author Comment

by:fisc
ID: 12218233
Well actually I DON'T have a backup of msdb from this server.  What I can get is a clean msdb.  I have another computer that I have Windows Server installed on in case of an emergency... I could just export the msdb from there if there is nothing truly crucial in msdb. Thanks for your help and putting up with my server administration newbie-dom.
0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 250 total points
ID: 12218667
Hmm, I don't think I would totally reinstall SQL Server.  At the very least, I would use the REBUILDM utility to rebuild the MASTER, MODEL, and MSDB databases.   After that runs, you could restore your master database back to the way it was and you should be in business.

Like was said above, you'll lose any scheduled jobs and DTS packages...
0
 

Author Comment

by:fisc
ID: 12219415
What command would I run to use REBUILDM?  There doesn't seem to be any errors in the other databases... I hadn't been backing up any of those databases you mentioned (I'm new to this)... the one that I am backing up daily is our company's customer database (which has no errors).  And actually there are no problems to speak of with this... the scheduled daily backup is running on our customer db, but if I run a manual backup it reports a torn page in msdb (but it actually does create the backup file).  So basically, everything's running great... I want to mess with as little as possible, just would like to get rid of the errors in msdb.  

I'm going to up this point value to 400, because I'd also like a little explaination of msdb so that I understand what I'm tampering with and what I risk losing in it.  It just stores the scheduled jobs and a history of when they are run?
0
 
LVL 34

Expert Comment

by:arbert
ID: 12219796
" It just stores the scheduled jobs and a history of when they are run? "

Yes, and any DTS packages....
0
 
LVL 10

Accepted Solution

by:
AustinSeven earned 150 total points
ID: 12224908

Well, I think I had adequately described MSDB...  

"MSDB is a system database that stores all the DTS packages, Jobs and schedules.   It is important and although your system is 'working', it's definitely worth fixing.    Apart from problems that might crop up in the future, you might actually have some problems being caused through the corrupt msdb database that you aren't aware of yet."

Arbert's REBUILDM option sounds like a good one.  I had clean forgotten about that option.   One to store away just in case I get a similar problem to fisc's one day.


AustinSeven
0
 

Author Comment

by:fisc
ID: 12279638
What is the syntax for REBUILDM?  Should I backup the master database now and then restore it after I run that? I won't lose my stored procedures in our customer database, will I?

Sorry, but I just obviouslly want to make sure I don't miss a step and screw something up.  I really would appreciate 1) do this 2) do that like AustinSteven did for the install option.
0
 

Author Comment

by:fisc
ID: 12280168
I knew I had read something about just getting a fresh msdb... This is what I was thinking of: instmsdb.sql  

Is that a viable option?
0
 
LVL 34

Expert Comment

by:arbert
ID: 12281232
yes, backup the master first, run rebuildm and it will recreate the system databases.  Restore the master...It won't affect the contents of the user databases (of course, back those up too).

instmsdb.sql is an SQL script that should recreate the database as well.  Probably a better option...
0
 

Author Comment

by:fisc
ID: 12374746
Thanks!  REBUILDM worked great!
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question