fisc
asked on
msdb consistency errors
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...?
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...?
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
AustinSeven
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
AustinSeven
ASKER
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?
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
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
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
" It just stores the scheduled jobs and a history of when they are run? "
Yes, and any DTS packages....
Yes, and any DTS packages....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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?
Is that a viable option?
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...
instmsdb.sql is an SQL script that should recreate the database as well. Probably a better option...
ASKER
Thanks! REBUILDM worked great!
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