MSDB is an important system database in Microsoft SQL server. The msdb database is used mainly by the SQL Server Agent to store system activities like sql server jobs, mail, service broker, maintenance plans, user and system database backup history, etc..It is also used by database engine and management studio.
Just like the user databases, we have to maintain and backup this database for the proper functioning of SQL Server Agent components.
There are certain DML and DDL operations which cannot be performed in the msdb database like:
- We cannot drop tables from this database.
- We cannot drop this database.
- We cannot make this system database to go off-line.
- We cannot do database mirroring on this database.
- We cannot rename this database name. etc…
Location of msdb database
If you open SQL Server Management Studio, you can see the msdb database under the System Databases.
The database files will be in the default data file location with the file names as MSDBData.mdf and MSDBLog.ldf. The logical file names will be MSDBData and MSDBLog.
Best practices to maintain msdb database:
- Set this database to full recovery model instead of it’s default simple recovery model.
- It is a good practice to take a regular backup of this database.
- Do not create user tables or any other user objects in this database.
- Do not modify any data in this database manually.
- Do not create any triggers for the system tables in this database.
- Delete backup history using sp_delete_backuphistory at least once in a month.
Related Articles
- Go here to know more about SQL Server.
- Go here to know more about DBMS and RDBMS.
Reference
- Go to Microsoft Article to know more about msdb database.