Solved

msdb consistency errors

Posted on 2004-10-04
13
765 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
  • 6
  • 4
  • 3
13 Comments
 
LVL 10

Expert Comment

by:AustinSeven
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 10

Expert Comment

by:AustinSeven
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:fisc
Comment Utility
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
Comment Utility
" 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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks!  REBUILDM worked great!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now