September 4, 2009 at 6:42 am
Hi
My msdb has been marked suspect. I checked on the web and came up with this.
I tried to start the service (sqlserver.exe) with the trace flag -T3608 , detach msdb and run the instmsdb.sql.
I shutdown the sqlserver service.
To start the sqlserver.exe with trace flag -T3608, i changed the startup parameters in the advanced tab of sqlserver service in configuration manager. On starting the service I get the error "The request failed or service did not respond in timely fashion. Consult event log or other applicable log for more details"
Then i tried to start the sql service using command prompt using the trace flag -T3608. I got the error "Your sql server installation is either corrupt or has been tampered with. Please uninstall thenre-run the setup to correct this problem." However iam able to start the sql service without the trace flag.
Thank fully this is on a server that is not in use but i shudder to think if this happens on a production server.
"Keep Trying"
September 4, 2009 at 6:55 am
Ok, not good.
Firstly, what did the SQL error log say about why MSDB was marked suspect? There would have been a reason in there.
If you remove the traceflag, does the SQL instance start?
Do you have a backup of msdb?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 4, 2009 at 7:14 am
Firstly, what did the SQL error log say about why MSDB was marked suspect? There would have been a reason in there.
This is what i got from the error log - An error occurred during recovery, preventing the database 'msdb' (database ID 4) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
If you remove the traceflag, does the SQL instance start?
Yes
Do you have a backup of msdb?
- No . This server does not have any databases or other objects that are used. It only has the adventureworks db to play around with.
I suspect this to be a hard disk error . I got an error while starting the system asking to run chkdisk.
"Keep Trying"
September 4, 2009 at 7:27 am
Chances are that it is a disk issue and that you were unlucky.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 4, 2009 at 7:40 am
Chirag (9/4/2009)
This is what i got from the error log - An error occurred during recovery, preventing the database 'msdb' (database ID 4) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
Anything else relating to MSDB? Any errors in the windows event log?
I suspect this to be a hard disk error . I got an error while starting the system asking to run chkdisk.
Does sound like it. Can you do some disk diagnostics? If it's not a production system, chkdisk's a reasonable place to start.
p.s. this is precisely the reason why a production server should have the system databases backed up, as well as the user databases. With a backup, this isn't that hard to recover from.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 6, 2009 at 6:13 am
maybe you can go back to your original msdb -> start sql server -> use emergency mode on msdb -> try some checkdb?
September 6, 2009 at 11:15 pm
Gail
Thanks for your response.
I got these 3 errors from the windows event log.
"Service Broker is disabled in MSDB or MSDB failed to start. Server level event notifications can not be delivered. Event notifications with FAN_IN in other databases could be affected as well.
"SQLServerAgent could not be started (reason: Unable to connect to server 'M141\SQL2005'; SQLServerAgent cannot start). "
"Error 3(The system cannot find the path specified.) occurred while opening file 'master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary."
I ran chkdsk and checked the event log. Nothing there. While running chkdsk for C: drive (where sql is installed) i saw a msg saying 2 reparse errors corrected.
Since the sql instance is not in use i haven't taken any backups.
"Keep Trying"
September 7, 2009 at 1:09 am
Chirag (9/6/2009)
"Error 3(The system cannot find the path specified.) occurred while opening file 'master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary."
Was that when starting with the traceflag or starting normally? If the former, it suggests that the startup options that give the location of master were incorrect. Check them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 7, 2009 at 1:55 am
GilaMonster (9/7/2009)
Chirag (9/6/2009)
"Error 3(The system cannot find the path specified.) occurred while opening file 'master.mdf' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary."Was that when starting with the traceflag or starting normally? If the former, it suggests that the startup options that give the location of master were incorrect. Check them.
I got this error while trying to start with the trace flag. I put the trace flag option in the startup parameters. I have removed them and that error does not seem to be cming again.
"Keep Trying"
September 8, 2009 at 12:40 am
Ok, I was after the original error messages about MSDB when it was marked suspect. Do you perhaps have that?
The error after adding the traceflag indicates that you added the traceflag incorrectly and messed up the other parameters, so SQL couldn't find the master database. The 3 that are already there must be left there (master data, master log, errorlog), then after a semicolon add the traceflag paramter
If you can't come right, because it's a test system, reinstall SQL is always an option.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2009 at 1:45 am
Thanks Gail, i did a reinstall.
"Keep Trying"
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply