MSDB in suspect mode.

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.

It sounds like you didn't detach msdb - see my post at http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/06/619304.aspx for the complete sequence of operations.

Let us know how it goes.

|||Hi,

As the DB was in the suspect mode it didn't gave me scope to detach the DB. Now the DB is out of suspect mode after succesfully completing the 3rd step of your blog.

However the another issue I found while going through the script (in your blog), instmsdb.sql while going to create msdb database uses the same path of master db. while actual path in the sysdatabase for msdb database is different than master db.

Also 3rd steps is completed succesfully but it doesn't create database anywhere due to this msdb is out of suspect mode but as it doesn't see physical file as per the path specified in sysdatabase it doesn't work and gives below error while accessing properties of any database:

Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space|||

I'm confused - you say msdb is out of suspect mode and then later you say that a new msdb wasn't created. Do you have a working msdb at this point?

[Edit] One thing I didn't ask is how did msdb get suspect in the first place? I take it you don't have a backup of msdb given that you're trying to rebuild it?

|||

Hi,

The database came out of suspect mode after replacing the good one msdb. However when I fire the query select * from sysdatabases the path of master db and msdb differs. My concern was when we run the query for instmsdb.sql which recreates msdb. My concern is it doesn't create the msdb file. So wanted to know whether it creates file or just repairs the existing one.

This was actually one of client's system so we do not know how it went to suspect mode. Unfortunately we do not have backups for the same.

Now when I right click on DB it gives me following error:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Danish_Norwegian_CI_AS" in the equal to operation. (Microsoft SQL Server, Error: 468)