Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

Does the Rebuild Master utility have a repair possibility?

Hello out there.
I just found out that MS SQLServer ver 8 has not done any backups since May 9'th. The msdb is marked SUSPECT. The Windows Server 2003 application log from May 10 talk about
Error: 823, Severity: 24, State: 2
I/O error (torn page) detected during read at offset 0x00000000bb2000 in file 'd:\apps\SQL Server\MSSQL\data\msdbdata.mdf'.
The Windows System log got corrupted at the same time, us having plenty power problems so I believe the SUSPECT.
I have no backup of the msdb - only production databases, and have not any install CD handy (Has to come down from Europe with DHL, I am in West Africa).
Looking up the Rebuild Master utility is says it can repair a corrupted Master DB. Can it also build a new MSDB. My Master is fine and so are the other DB's, so I would very much like to avoid to run scripts and reload data if possible.
I will be back online Monday.
Kind regards Peter Vibede
0
PVibede
Asked:
PVibede
  • 4
  • 2
  • 2
2 Solutions
 
imran_fastCommented:
try running this from query analyzer:
sp_resetstatus yourdatabasenamehere

then

DBCC CHECKDB ('msdb',REPAIR_FAST)
GO
0
 
MikeWalshCommented:
From that link that Imran provided you:

"Caution  Use sp_resetstatus only when directed by your primary support provider or as recommended in Troubleshooting. Otherwise, you might damage your database."

Obviously the database is currently having problems, but keep that in mind here, resetting a status is something support will normally direct you to and I believe a waiver is required..

MSDB is not crucial to have up to date in that your application will still run. I would either restore to an older backup and then recreate any jobs/maintenance plans that were added after that backup, or look at this posting to learn about a SQL script that will rebuild MSDB:

http://dbforums.com/showthread.php?p=4545573
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
MikeWalshCommented:
From the above link:

use
1.. In SQL Server Enterprise Manager, right-click the server name and
click Properties.
2.. On the General tab, click Startup Parameters.
3.. Add a new parameter as "-T3608" (without the quotation marks).
After you add trace flag 3608, follow these steps:
1.. Stop, and then restart SQL Server.
2.. Make sure that the SQL Server Agent service is not currently running.
3.. Detach the msdb database as follows:
use master
go
sp_detach_db 'msdb'
go
4.. Delete or rename the msdb mdf and ldf files.
5.. Run the INSTMSDB.SQL script
6.. Remove the -T3608 trace flag from the startup parameters box in
Enterprise Manager
7.. Stop, and then restart SQL Server.

0
 
MikeWalshCommented:
Keep in mind, when rebuilding MSBD, any jobs, maintenance plans and backup history will be lost. This is not the end of the world if you keep a Run Book or know which jobs existed on your server previously. Also if your jobs don't change much, restoring from the backup isn't the end of the world.. Backup History is not necessary and most of your jobs will be the same.

Your DTS packages that are stored in SQL are also in MSDB..
0
 
PVibedeAuthor Commented:
Mike Walsh & imran_fast
Thank You for your help. I will now study and have a go at reset status first to see if SQLServer can get out of trouble by itself. If not go through the install msdb excersize (will probably do this anyway as I do not trust files that has shown i/o errors too much).
The Maintenance plans can easily be reapplied. This is a simple installation.
Should be ready to have a go this afternoon late, after our people has gone home.
Peter

0
 
MikeWalshCommented:
PVibede - Just keep in mind that the reset status is not fully supported as it should only be run at the behest of MS Support and when doing so you sign a waiver with them.. This is usually a last resort sort of step, and if you have a recent backup that has your jobs and maint. plans in it, you are better off just restoring that. Good luck all the same.
0
 
PVibedeAuthor Commented:
Mike & Imran
Thanks for your speedy help.
I chose the detach, rename and install msdb route. no problems.
Had to wait for some quiet time without SQL users.
Have now included master & msdb in maintenance plans.
Kind regards Peter
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now