September 6, 2007 at 10:33 am
Hi,
I have mssql 2005 installed and i see that MSDB database is marked as suspect. I almost tried all the steps which are submitted in the forums.
1) I shut down the server, navigating to the directory 'd:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn' and doing the following: start sqlservr.exe -c -T3608
2) renamed the damaged msdb files (msdbdata.mdf and msdblog.ldf in the 'd:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data' directory)
3) Run the instmsdb.sql script in the 'd:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install' directory
--x---
When I executed third step it returned me below error:
Msg 926, Level 14, State 1, Line 63
Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Please advice me.
September 10, 2007 at 9:37 am
Try as specified in the below link and revert,
http://sql-articles.com/articles/HA/suspectdb.htm
[font="Verdana"]- Deepak[/font]
September 10, 2007 at 4:25 pm
try to see the folder as required permission in which MSDB file resides. some time not having required permission on folder makes database suspect (user\system). i hope this link may help.
http://www.mssqltips.com/tip.asp?tip=1208
thx
September 11, 2007 at 9:12 am
Since it's MSDB it's easy enough to just detact and rebuild if you don't care about the backup data and scheduling information it stores. Normally I don't because it only takes about 10 minutes to setup any agent jobs again unless they're overly complex.
1. Add a new parameter/trace flag as "-T3608" (without the quotation marks) in the startup parameters.
2. Stop, and then restart SQL Server.
3. Detach the msdb database as follows:
use master
go
sp_detach_db 'msdb'
go
OR
Right click in enterprise manager and detach database.
4. Delete or rename the msdb mdf and ldf files.
5. Search you SQL Server 2000 install for a file named instmsdb.sql (should be there, if not I'll post).
6. Run the INSTMSDB.SQL
7. Remove the -T3608 trace flag from the startup parameters box in Enterprise Manager
8. Stop, and then restart SQL Server.
Every once in a while I have to put it into single user mode when this happens, but this usually only takes about 20 - 30 minutes to recover.
good luck!
September 19, 2007 at 11:57 am
Thanks for all help. The issue has been resolved.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply