Solved

msdb consistency errors

Posted on 2004-10-04
13
768 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
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
 
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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

867 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

15 Experts available now in Live!

Get 1:1 Help Now