By: Eric Blinn
Overview
Common things to know about the MSDB database include the location of the files, moving the MSDB database files and the files that make up the MSDB database.
What physical files, names, locations, etc. support the MSDB database?
The MSDB database is made up of 2 files - one data file and one log file. The data file has the logical name "MSDBData" and the default file name of "MSDBData.mdf". The log file is "MSDBLog" and "MSDBLog.ldf" respectively.
This is the minimum number of files for any database and MSDB is no exception. SQL Server does allow for additional files to be added to MSDB, but it’s hard to imagine a MSDB database getting so big that multiple files are necessary.
SQL Server will let you rename either the physical or logical names of these 2 files, but there is really no reason to complete an operation like this. It serves no purpose and would add an unnecessary layer of complexity to the environment.
Where do I find the MSDB database on disk and in SSMS?
There are a few ways to find the files in SSMS. They can be read from the sysfiles DMO.
select name, filename from MSDB.sys.sysfiles;
The files will also be listed in the database properties window as seen in the previous screenshot.
When browsing on disk, the files look like any other set of database files.
Does it make a difference where the MSDB database files reside (do they need to be on C:\, SAN, etc.)?
The MSDB database files should be stored with the other database files of the same type. This generally means NOT on the C: drive. The screenshots in this post are showing the C: drive only because they are from a local instance on this author’s laptop that contains only a C: drive.
Starting with SQL Server 2012, the installer started offering a choice about where to store the system database files. Prior to that they would be installed with the SQL Server binaries and have to be moved to the target location post installation.
Can the MSDB database be moved?
Yes! The MSDB database files can be moved. The are 2 methods to move the MSDB database files. One is a more common method, especially during a new install with limited users or uptime requirements. The other lets the SQL Server instance stay up and interrupts only the MSDB-dependent parts of the operation such as the SQL Server Agent.
Option 1
This is the more common method that is generally suggested by the result of a Google search.
Step 1- Inform SQL Server that during the next startup of the service the MSDB database data and/or log file(s) will be somewhere else.
During this step it will allow the file name to be changed, but it is not advised to change the physical file name or extension.
The following code will change the expected location of the MSDB database data and log files respectively from wherever they currently are to the root C drive. It assumes the default logical names.
ALTER DATABASE [MSDB] MODIFY FILE ( NAME = MSDBData , FILENAME = 'C:\MSDBData.mdf' ); ALTER DATABASE [MSDB] MODIFY FILE ( NAME = MSDBLog, FILENAME = 'C:\MSDBLog.ldf' );
Executing these commands will give a very clear output message.
The file "MSDBData" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "MSDBLog" has been modified in the system catalog. The new path will be used the next time the database is started.
Step 2 -Now that SQL Server has been informed of the new location(s), stop the SQL Server and SQL Server Agent services so that it will release its lock on those 2 (or more) MSDB database files.
Step 3 -Move the file(s) to the intended location(s) that were given to SQL Server in step 1.
Step 4 -Start the SQL Server service.
SQL Server should start right up. Immediately check MSDB in SSMS. Does it look like the screenshot from the very beginning of this tutorial with the words "(Recovery Pending)" at the end? If so, there is a problem with the file location of the MSDB files. Perhaps they aren’t in the same place the catalog thinks they are or maybe they are in the right place, but the service account can’t see them there due to a permission issue. Sometimes the Event Viewer, if using Windows, offers more details as to why some databases don’t come online and should be consulted. In any case, work to resolve that issue and consider putting the files back, if necessary, to get the instance up and running again.
Option 2
Start by stopping the SQL Server Agent service and kill any other processes that might be using MSDB. Then, simply back up the MSDB database and immediately restore MSDB with the REPLACE option and use the MOVE keyword to select a new location for any files.
BACKUP DATABASE [msdb] TO DISK = N'C:\backups\MSDB.bak'; GO RESTORE DATABASE [msdb] FROM DISK = N'C:\backups\MSDB.bak' WITH REPLACE, MOVE N'MSDBData' TO N'C:\Backups\MSDBData_NEW.mdf', MOVE N'MSDBLog' TO N'C:\Backups\MSDBLog_NEW.ldf'; GO
Once the restore finishes successfully, restart the SQL Server Agent.
