MS SQL Server has mainly four system databases they are Master database, MSDB database, Tempdb, and Model database.
Master Database: The master database stores all the system level information of the SQL Server like system configuration and logging account settings.
Tempdb Database: A global resource holds information of all the temporary objects like temporary tables and stored procedures.
Model Database: The Model database acts as a template for all databases created on a SQL Server.
MSDB Database: The MSDB database is used for storing all the information related to DTS Package, Database Backups, Replication, SQL Agent information (like SQL Server jobs, Maintenance plans, mails) etc. One cannot drop or rename the MSDB database.
The system databases should be maintained for proper functioning of SQL Server.
|File Type||Logical File Name||Physical File Name|
|Primary data||MSDB Data File||msdbdata.mdf|
|Log||MSDB Log File||msdblog.ldf|
Size of MSDB database may vary for different editions of MS SQL Server.
Location of MSDB Database: On SQL Server Management Studio, inside Object Explorer, you can see the MSDB database under the System databases.
The MSDB Data file and MSDB Log file will be present in the default file location for data files with the names MSDB data and MSDB log. You can view these files at C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
Why MSDB Database Goes Into Suspect Mode?
There can be several reasons for a MS SQL Server to get in a suspect mode. These are:
- MSDB data file or MSDB log file have got corrupt or damaged.
- Unavailability or crashing of database files
- System failure in which SQL Server is installed
- Virus intrusion in a SQL Server system
- Device having the SQL Server is going offline
- Lack of Disk space to store MSDB database.
- Improper shutdown can also cause the suspect mode of MSDB database.
Consequences: You can get an error message as you start MS SQL Server Management Studio or while accessing the database from visual studio. It might be display, “MSDB was in suspect state (SQL Server Error 926)” and you need to repair or recover suspect MSDB database and following window can appear on your screen.
Solution to Recover Suspect MSDB Database
Recover Suspect MSDB Database by using MS SQL Server Management Studio
You can follow the below mention steps to recover suspect MSDB database.
- Open MS SQL Server Management
- In Object Explorer, Right click on SQL Server 10.5 and then Click on Stop
- Open Control Panel Window, Click on Administrative Tools and then click on Services
- Select SQL Server (MS SQL Server) and then Right click on Stop
- Go to C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLServer\MS SQL\Data
- Move the MSDB data and MSDB Log file to any other location or system
- Copy both the files from new location and place it on the older location
- Now open Object Explorer and then Right Click on Start
- Now refresh your database and you can detach the MSDB file
Recover Suspect MSDB database by Using T-SQL Query
You can recover suspect MSDB database by executing following Query in SQL Server.
Here, test is the name of database, which is in suspect mode.
This code will perform following tasks.
It will reset the suspect Flag and set the database as read only by altering the database to emergency mode. Then it will set the database to single-user mode, repair the errors and then set the database to multi-user mode.
You can easily recover suspect MSDB database by using T-SQL Query or by using MS SQL Server Management Studio. But if the method stated above fails, then it is recommended to use SQL repair utility to resolve the problem which occurs due to Suspect mode of MSDB database.