I've just spent a bunch time researching an answer to this question on the new disaster recovery forum because I couldn't find any definitive info on how to do this in SQL Server 2005. I pieced together a method to do this on previous releases of SQL Server and tried it on one of the SQL Server 2005 instances on my laptop to make sure it works. I'd like to share it with everyone to save someone having to do the same.
If your msdb goes suspect then you have two choices, restore it from a backup or recreate it (and then recreate any scheduled jobs). Obviously everyone has a comprehensive and valid set of backups, right? If only...
Of course, the very first thing you do is work out why it went suspect in the first place and take any necessary steps to stop it happening again.
Now you'd hope that if you don't have a valid msdb backup then you can at least run repair on it and so you don't lose everything in there. Well, that works as long as the transaction log isn't damaged. Ok, but then surely we can stick the database into the now-documented emergency mode (alter database dbname set emergency) and run emergency mode repair? (dbcc checkdb (dbname, repair_allow_data_loss) in emergency mode). Nope, msdb can't be put into emergency mode.
So, you're out of options and you're going to have to recreate msdb. Here's what to do (change the directory paths to suit your installation):
This works on SQL Server 2000 as well.
Hopefully you'll never have to do this but if you ever do, let me know how it goes for you.
Great information. Thanks! But...
Still have a problem. Installed MSDE2K as an instance and ran sp3 on that instance.
Sp setup breaked at running scripts, saying database msdb log file was not good, look at the SQL log file (where no info was...)
Than stumbled over this usefull piece of information. Tried this, but this is not ok. Could you prompt me what's the problem?
C:\Program Files\Microsoft SQL Server\MSSQL$DRUMIS\BinnMSSQL$DRUMIS\Binn>sqlservr.exe -c -T3608 -s DRUMIS
C:\Program Files\Microsoft SQL Server\80\Tools\Binn>OSQL.EXE -S"RATIOSERVER\DRUMIS" -d"Master" -U********** -P********** -i"C:\Program Files\Microsoft SQL Server\MSSQL$DRUMIS\DataMSSQL$DRUMIS\Install\instmsdb.sql"
2006-11-14 17:10:34.07 server Microsoft SQL Server 2000 - 8.00.760 (Intel X8
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.2 (Build 3790: )
2006-11-14 17:10:34.09 server Copyright (C) 1988-2002 Microsoft Corporation.
2006-11-14 17:10:34.09 server All rights reserved.
2006-11-14 17:10:34.09 server Server Process ID is 2100.
2006-11-14 17:10:34.09 server Logging SQL Server messages in file 'C:\Program
Files\Microsoft SQL Server\MSSQL$DRUMIS\DataMSSQL$DRUMIS\LOG\ERRORLOG'.
2006-11-14 17:10:34.11 server SQL Server is starting at priority class 'norma
l'(2 CPUs detected).
2006-11-14 17:10:34.29 server SQL Server configured for thread mode processin
2006-11-14 17:10:34.29 server Using dynamic lock allocation.  Lock Block
s,  Lock Owner Blocks.
2006-11-14 17:10:34.34 spid4 Recovering only master database
2006-11-14 17:10:34.34 spid4 Starting up database 'master'.
2006-11-14 17:10:34.53 server Using 'SSNETLIB.DLL' version '8.0.766'.
2006-11-14 17:10:34.56 spid4 Server name is 'RATIOSERVER\DRUMIS'.
2006-11-14 17:10:34.57 server SQL server listening on 192.168.1.100: 3676.
2006-11-14 17:10:34.57 server SQL server listening on 127.0.0.1: 3676.
2006-11-14 17:10:34.59 spid4 Recovery complete.
2006-11-14 17:10:34.59 spid4 SQL global counter collection task is created.
2006-11-14 17:10:34.59 server SQL server listening on TCP, Shared Memory, Nam
2006-11-14 17:10:34.59 server SQL Server is ready for client connections
2006-11-14 17:10:34.61 spid4 Warning: override, autoexec procedures skipped.
2006-11-14 17:11:05.10 spid51 Starting up database 'D4WData'.
2006-11-14 17:23:41.42 spid51 DBCC TRACEON 1717, server process ID (SPID) 51.
2006-11-14 17:23:41.90 spid51 Clearing tempdb database.
2006-11-14 17:23:41.90 spid51 Starting up database 'model'.
2006-11-14 17:23:42.23 spid51 Starting up database 'tempdb'.
2006-11-14 17:23:42.31 spid51 Error: 15457, Severity: 0, State: 1
2006-11-14 17:23:42.31 spid51 Configuration option 'allow updates' changed fr
om 1 to 1. Run the RECONFIGURE statement to install..
2006-11-14 17:23:42.71 spid51 Starting up database 'msdb'.
2006-11-14 17:23:42.71 spid51 udopen: Operating system error 2(error not foun
d) during the creation/opening of physical device C:\Program Files\Microsoft SQL
2006-11-14 17:23:42.73 spid51 FCB::Open failed: Could not open device C:\Prog
ram Files\Microsoft SQL Server\MSSQL$DRUMIS\DataMSSQL$DRUMIS\Data\msdbdata.mdf f
or virtual device number (VDN) 1.
2006-11-14 17:23:43.14 spid51 Error: 50000, Severity: 20, State: 127
2006-11-14 17:23:43.14 spid51 A problem was encountered accessing msdb. INSTM
Starting execution of INSTMSDB.SQL
Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 5105, Level 16, State 4, Server RATIOSERVER\DRUMIS, Line 57
Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL$DRUMIS\DataMSSQL$DRUMIS\Data\msdbdata.mdf' may be incorrect.
Msg 945, Level 14, State 2, Server RATIOSERVER\DRUMIS, Line 57 Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space.
See the SQL Server errorlog for details.
Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL$DRUMIS\DataMSSQL$DRUMIS\Data\msdblog.ldf' may be incorrect.
Msg 945, Level 14, State 2, Server RATIOSERVER\DRUMIS, Line 1
Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Server RATIOSERVER\DRUMIS, Line 1
ALTER DATABASE statement failed.
sp_dboption command failed.
Msg 945, Level 14, State 2, Server RATIOSERVER\DRUMIS, Line 2
Msg 50000, Level 20, State 127, Server RATIOSERVER\DRUMIS, Line 4
A problem was encountered accessing msdb. INSTMSDB.SQL terminating.
Looks like the pathname is screwed up (the section "MSSQL$DRUMIS\Data" is repeated - did you enter it manually? Please shoot me email rather than debugging this through the comments - thanks
Solved the problem by installing MSDE on another computer.
Stopping both SQL servers.
Copy msdb.mdf and msdb.ldf from good installation to bad installation and started SQL server again.
Msdb was now correctly recognized. - thanks
In sql 2005, I also got the same insufficient memory error unable to connect to MSDB DB, I just tried the step 1 with -T3608 Parameter, then tried to stop and restart the sql server.Then i opened the sql server managment studio, it was now able to connect to MSDB DB without no error, before i tried with detach,move and recreate the MSDB DB.So i stopped with Step 1 with trace flag option,then removed the trace flag option in startup parameter.It works fine now.
Thanks for the tips.
It's the second time that I got an ugly message from my SQL Server: Database 'msdb' cannot be opened...
Got some problems with dns can create dns server cuz it cant connect to sql server i got this error from dns
Microsoft SQl server login
SQL state error:'01000'
SQL server error:10061
[Microsoft][ODBC SQl server driver][TCP/IP sockets]Connectionopened(connect())
Sql server error:17
[Microsoft][ODBC SQl server driver][TCP/IP sockets]SQL server does not exist or access diened"
I have gone through this exercise. If you have installed SP2 on 2005 and follow the steps above then you will get many problems with Mails. By following the above process, you basically uninstall part of SP2 by running the instmsdb.sql file. The problem is you don't see any real error messages, your emails just stay queued and never sent. A test is stored proc sysmail_delete_profile_sp should contain 3 arguments not 2 if you are on SP2.
To fix it, I had to uninstall the 3054 fix + SP2 then re-install SP2 + 3054 and you should be fine. I think Microsoft should provide an updated instmsdb.sql as part of the SP2 installation. Or something similar.
I have used -m as well as my argument and & please make sure that nothings is connected to your sql server.
When i am running the thrid step it is showing in error logs for good backup of msdb database.
what to do now
PingBack from http://thought.mobiforumz.com/2006/09/08/fixing-suspect-sql-databases/
PingBack from http://winzenz.mobiforumz.com/2006/09/08/fixing-suspect-sql-databases/