Share via

MSDB in suspect mode.

  • Article

Question

Thursday, September 6, 2007 4:18 PM

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.

All replies (9)

Thursday, September 6, 2007 6:19 PM âś…Answered

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.

 

 

Thursday, September 6, 2007 7:44 PM

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

Thursday, September 6, 2007 8:47 PM

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?

Friday, September 7, 2007 5:56 PM

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)

Tuesday, August 16, 2011 7:29 AM

The other very simple steps to recover the MSDB database from suspect mode is :-

1. Stop the SQL Server database & services

2. Rename or move the "msdblog.ldf" & "msdbdata.mdf" files

2. Copy "msdblog.ldf & msdbdata.mdf" files from any other working database installation to the same path

**3. Start the SQL Server database **

This will make your MSDB up & running
:) hope this helps

Regards,
Ravi Thapliyal

Thursday, September 1, 2011 1:50 PM

Hi Ravi,

This is not working in my case.

Thursday, September 1, 2011 2:19 PM

Hi Ravi,

This is not working in my case.

Hi,

This is a very old thread.  If you have a problem, you're better off starting a new thread.

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Monday, October 24, 2011 5:39 AM

hi

SQL particular database is corrupted so you have to restore the backup to get it right!!!

Thursday, March 20, 2014 5:59 AM

Hi,Ravi

this method is working

thanks