By: Eric Blinn
Overview
In addition to the tables where the data is stored, MSDB also includes several stored procedures you can execute directly.
What are the key stored procedures in the MSDB database that are important for SQL Server Professionals to know about?
This is another situation where an entire book could be dedicated to these procedures. Here is a sampling of some of the more popular procedures.
exec sp_help_jobactivity
The procedure sp_help_jobactivity gives a snapshot of all SQL Server Agent Jobs including their current execution status and the details of the most recent execution. It is very useful when monitoring for jobs that should be running, but may not be. Such jobs would have a valid start_execution_date value, but would have a NULL value for stop_execution_date.
exec sp_start_job exec sp_stop_job
This pair of procedures will start or stop a SQL Server Agent Job respectively. They will accept either a job name or job id as a parameter. The start command will also optionally accept a step id if the jobs needs to start at a place other than the default.
These procedures can be useful when trying to automate certain tasks or automatically recover from failures.
exec sp_send_dbmail exec sp_notify_operator
This pair of procedures can be used to send email messages from a SQL Server if DBMail has been set up. sp_send_dbmail is usually the more popular procedure as it accepts all usual email related options such as to, cc, bcc, subject, body, and attachments –among others. One of the more popular optional parameters is the ability to include the output of a query in the message.
sp_nofity_operator has far fewer options, but accepts either an operator name or ID instead of an email address. This layer of abstraction may be useful to some administrators as the operator’s underlying email address can be changed when personnel groupings change rather than having to change code to account for a new email address. Similar functionality can be reached by always using mailing lists when writing code that uses sp_send_dbmail.
Can I see the code for the MSDB system objects and if so, how?
The system procedures can be viewed by finding them in the Object Explorer under Programmability, Stored Procedures, and, finally, System Stored Procedures. The "Modify" option of a context menu will bring up the object definition.
There is value in reviewing some of these objects as it is a great way to learn more about the schema of MSDB to better write queries against the objects.
Can I change these objects in the MSDB database?
Just like the tables and views, SQL Server will allow the system procedures to be changed. For the same reasons the tables and views should not be changed, neither should the procedures.
Last Update: 3/31/2020
