Daniel Calbimonte

The system msdb database, introduction and tips

June 14, 2016 by

Introduction

In a previous article, we talked about the tempdb system database. In this article, we will talk about the msdb database.

The msdb database stores information like the SQL Server Agent information, backup information, log shipping, maintenance plans.

In this article we will describe internal system tables of the msdb database and create some jobs to see the tables used.

Requirements

  • SQL Server installed (we are using the SQL 2016, but earlier versions are valid).

Getting started

Backup system tables

  1. We will start creating a backup to show how the data is stored in the msdb.

  2. In SQL Server Management Studio (SSMS), right click on a database and select the option Tasks>Back up


    Figure 1. Backup option

  3. Press OK to backup with the default values:


    Figure 2. Backup options

  4. In the msdb database, you can check the backup information in the backup system tables. For example, you can check the backup file information in the dbo.backupfile:


    Figure 3. The backupfile system table

  5. You can check information like the file size, physical location of the backup, etc:


    Figure 4. The backup file information

  6. In the dbo.backupset system table you can find the name of the backup and the backup configuration options like the backup size, encryption information and more:


    Figure 5. The backupset information

  7. As you can see, all the information is stored in the MSDB system views. If you create a backup it is a good practice to backup the MSDB database also to track the changes.

    8. Log Shipping system tables

    1. Other tables used in the MSDB database are the Log Shipping system tables. These tables are used to restore the Log Shipping information. Log Shipping is a feature used to have a secondary server ready in case that the primary server fails. This method uses Transaction Logs to replicate the data. For more information about Log Shipping, you can read this related article: How to create SQL Server Log Shipping

      You can see the list of system tables in the following picture:


      Figure 6. Log Shipping system tables

    2. Whenever you change the Log Shipping configuration option it is a good practice to backup the msdb database.

      3. SQL Agent System tables

      1. In the demo we will create a Database Backup using PowerShell in the SQL Agent and show the tables used.

      2. In the SQL Server Agent node right click on Jobs and select new job:


        Figure 7. Creating a new job

      3. Add a name for the job:


        Figure 8. General job information

      4. In the step page, press the New button:


        Figure 9. Job steps

      5. Specify a name. In the type, select Power Shell and in the Command, write the following commands:

        Backup-SqlDatabase –ServerInstance “OLAPSQLDAN” –Database “Test”

        The backup-sqlDatabase cmdlet, is used to backup databases. In this example, we are backing up the Test database in the OLAPSQLDAN Server:


        Figure 10. Powershell Backup Cmdlet

      6. We can define a Schedule for the job. In the Schedule page, press the new button:


        Figure 11. New Schedule

      7. Add a name and specify the schedule to run the job:


        Figure 12. Schedule information

      8. In the Alert section, press the Add button:


        Figure 13. The Alerts page

      9. We will generate an alert if the Database file of the test database rises above 10,000 Kb:


        Figure 14. Alert options

      10. Press OK to save the job. Once the job is create right click the Operators folder and select New Operator:


        Figure 15. Creating a new operator

      11. Add a Name and an email:


        Figure 16. New operator properties

      12. Save the operator information.

      13. Open the Job created before. Go to the notification page. Check the E-mail and select the operator created on step 11:


        Figure 17. Job notification

      14. Start the job:


        Figure 18. Starting a job

      15. You will receive a success message after starting the job:


        Figure 19. Success job message

      16. You can check that the backup was created successfully:


        Figure 20. The backup created

      17. All the job information is stored in the msdb system tables. For example the execution information is stored in the sysjobactivity:


        Figure 21. The job activity table

      18. The job activity table contains the information about the executed jobs:


        Figure 22. The table information

      19. The sysjobhistory contains historical information like the error messages and the execution date and time:


        Figure 23. The sysjobhistory table

      20. You can also see the success messages and operator information:


        Figure 24. The sysjobhistory data

      21. The sysjobs table contains the list of jobs available:


        Figure 25. The sysjobs table

      22. The sysjobschedules contains the schedules created in figure 12 :


        Figure 26. The data in the sysjob table

      23. The sysjobschedules contain the schedules created in figure 12:


        Figure 27. The sysjobschedules

      24. The sysjobsteps table contains the job steps created in figure 10:


        Figure 28. The PowerShell job step

      25. We can also see the job alerts created on the figure 14 using the sysalerts:


        Figure 29. The sysalerts system table

      26. In the sysoperators table, you can see the operator created in figure 16


        Figure 30. The sysoperators table

      Whenever you make important changes in the SQL Server Agent it is a good practice to backup the msdb database.

      How can I restore the msdb database if I do not have a backup?

      You can rebuild your msdb database using the instmsdb script included in the MSSQL\Install folder. For more information about restoring system databases, refer to the following link:

      How can I hide the msdb database in the SSMS?

      If you want to hide the system database you can do so in SSMS. Go to Tools>Options>Startup and selecting the option Hide system object form Object Explorer:

      You will need to close and reopen the SSMS. Once it is done, the system tables including the MSDB will not be visible in the solution explorer:


      Figure 32. The system databases hidden

      However, you can query the system tables using T-SQL.

      Conclusions

      The msdb database is an important database used to store backup, Log Shipping and Agent information. Backup your msdb datatabase whenever you create backups, when you change the SQL Agent information or when you change the log shipping information or when you modify information that is stored in the msdb database.

      Other recommendations

      • Make sure to store the MSDB transaction log in a fault tolerant storage device.

      • Set the recovery model to full (by default it is simple).

      References

      For more information, refer to these links:

      Daniel Calbimonte
      Latest posts by Daniel Calbimonte (see all)

      Related posts:

      1. SQL Server system databases – the msdb database
      2. The tempdb database, introduction and recommendations
      3. How to retrieve information about SSIS packages stored in MSDB Database
      4. How to create SQL Server Log Shipping
      5. How to get a SQL database restore history
General database design

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams. He also helps with translating SQLShack articles to Spanish View all posts by Daniel Calbimonte

168 Views