Skip to main content
Sign Out

Welcome to Dell

My Account
  • Place orders quickly and easily
  • View orders and track your shipping status
  • Create and access a list of your products
  • Manage your Dell EMC sites, products, and product-level contacts using Company Administration.
Sign In Create an Account Premier Sign In Partner Program Sign In
Contact Us

Your Dell.com Carts

Some article numbers may have changed. If this isn't what you're looking for, try searching all articles. Search articles

Article Number: 000200606

Microsoft SQL Server MSDB Database Recovery

Summary: Information on how to recover a Microsoft SQL Server MSDB database

Article Content

Symptoms

When damaged and corrupted a MS SQL Server MSDB database may not come online and be operational.  The SQL Server Agent Service will not start, and any scheduled jobs will not run automatically or manually.

Cause

SQL Server database file damage can occur for many different reasons.  A majority of MS SQL Server damaged MSDB database events occur because of hardware issues related to memory, disk, or processor problems.

Resolution

Three methods to recover MS SQL MSDB databases:
  1. Restore from backup
  2. Use DBCC CheckDB to repair MSDB database
  3. Replace the MSDB database files
MSDB database restore from backup:
The restore process using SQL Server Management Studio requires a current backup and can be easily performed by going to properties of the “msdb” database, Tasks -> Restore-> Database and pointing to the location of backup media.

image.png

Use DBCC CheckDB for Database repair:
If no backup is available, we can run “DBCC CheckDB” to save the current MSDB database and its contents.  The T-SQL commands below check the integrity of the MSDB database, run a soft repair of the MSDB database, and finally a hard repair of the MSDB database:
 
  • MSDB integrity check, is the database free of corruption?
Run command below in SSMS query window:
USE Master;
      GO
        ALTER Database MSDB
        SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
        GO
        DBCC CHECKDB (msdb, NOINDEX);
        GO
        ALTER DATABASE MSDB
        SET-MULTI_USER;
        GO
We want to check the line at bottom of return window reporting “allocation” and "consistency” errors.  A damaged MSDB database will report errors and require the DBCC CheckDB commands below to repair.

  • MSDB T-SQL command for an attempted “soft recovery” with no data loss:
Run command below in SSMS query window:
USE Master;
      GO
        ALTER Database MSDB
        SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
        GO
        DBCC CHECKDB (msdb, REPAIR_REBUILD);
        GO
        ALTER DATABASE MSDB
        SET-MULTI_USER;
        GO
After running command above, run DBCC CheckDB again to see new status of database.
  • MSDB T-SQL command for an attempted “hard recovery” with possible data loss:
Run command below in SSMS query window:
USE Master;
      GO
        ALTER Database MSDB
        SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
        GO
        DBCC CHECKDB (msdb, REPAIR_ALLOW_DATA_LOSS);
        GO
        ALTER DATABASE MSDB
        SET-MULTI_USER;
        GO
After running command above, run DBCC CheckDB again to see new status of database.
 
Replace the MSDB database files
If the MSDB database is corrupt and DBCC repair options have not worked; we can put in a new MSDB database by replacing the MSDB database files.
When we replace the MSDB database files we lose all information like custom backup jobs, task information, replication, log shipping, and maintenance plan data.
Steps below are used to replace an existing MSDB database with a new default MSDB database.
 
  • Browse to the SQL Server installation directory and go to the "Templates" folder under the “Binn” directory:
image.png

The “msdbdata.mdf” and “msdblog.ldf” files are the MSDB database template files we need to bring up a new stable MSDB database.
  • Need to stop the SQL Server Service to replace the existing MSDB database files.
image.png
  • Next go to the SQL Server Data directory.  Directory path in example below is “C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA”

 
  • Find the corrupt “msdbdata.mdf” and “msdblog.ldf” files.
image.png
  • Copy and move the files to another directory; in this case we’re moving them to a folder called “MSDB_Save”.  There is a chance these files can be imported into another SQL Server installation and checked again with DBCC CheckDB; it isn’t a best practice to delete corrupt database files when troubleshooting.
                 image.png
 
  • Find where the MSDB temp database files are saved and put a copy of them back into our default SQL Server database directory:
image.png
  • Final location for the MSDB database temp files; back in the default data directory of “C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA”.
image.png
  • After replacing the MSDB database files, start the SQL Server Service.
image.png

Article Properties

Product

Dell Quickstart Data Warehouse Appliance, Microsoft Windows Server 2016, Microsoft Windows Server 2019, Microsoft Windows Server 2022, Microsoft Windows 2012 Server, Microsoft Windows 2012 Server R2, Prosupport for Microsoft Software

Last Published Date

02 Mar 2023

Version

3

Article Type

Solution

Back to Top