May 29, 2003 at 12:23 pm
MSDB is showing suspected and we can not shutdown the SQL server to reset the status. Does anyone know how change it's status without stopping SQL server. We had differential & transactional backup running at regular intervals but now all the schedules job is gone.
Help is appreciated.
May 29, 2003 at 1:37 pm
U don't need to shutdown sql server to reset suspect status, but i am not sure about MSDB db requirments. In general you should have SA rights to use sp_reconfigure proc to reset the status.
May 29, 2003 at 2:01 pm
You can use the sp_resetstatus system proc. That will try to force the database online/healthy. You have to stop/start the server to take affect, which may be a problem from what you said.
...If that doesn't work, you can force the DB into emergency mode by setting the "status" field for that DB in sysdatabases to 32768. MAKE SURE TO NOTE WHAT THE STATUS WAS BEFORE YOU CHANGE IT! You must also bounce the server for this to take affect. Note: you must "allow updates" to system tables before updating the status. This method is undocumented and is usually a LAST RESORT if you must get the data out of the database.
BTW, I'm not sure if this will work for "msdb". Also, any idea what caused it to go suspect...hardware/disk failure?
-Dan
-Dan
May 29, 2003 at 2:10 pm
The best way is to restore it from the backup (hope you were making backups of your system databases???). Otherwise, you need to determine the reason for msdb to go into suspect mode. Check the log size on the file system, could have run out of disk space. Check Event Viewer/Application Log for any postings regarding SQLAgent or msdb. Of course, try sp_resetstatus, but if all fails, - your option will be to set SQLServer service to manual, bounce the box, and copy both data and log files from the CD to a different location, start the service with -T3608, and try sp_detach_db/sp_attach_db. Good luck!
Robert.
May 29, 2003 at 2:49 pm
Robert will this work with user DB also ? or just System DB only.
Shas3
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply