Where are the SQL Jobs are Stored in SQL Server

Introduction

In this article, We will discuss where the SQL Jobs are stored in SQL Server. Most of us know the purpose of SQL Job, A job is a specified series of actions that SQL Server Agent performs. Use jobs to define an administrative task that can be run one or more times and monitored for success or failure. A job can run on one local server or on multiple remote servers. How many of us know where these SQL Jobs are Stored?.

dbo.sysjobs Table

All SQL Jobs are stored in dbo.sysjobs table under MSDB. Stores the information for each scheduled job to be executed by SQL Server Agent.

SELECT 
	* 
FROM 
dbo.sysjobs
Fig.1 sysjobs Results

The columns in this table indicates,

Column nameData typeDescription
job_iduniqueidentifierUnique ID of the job.
originating_server_idintID of the server from which the job came.
namesysnameName of the job.
enabledtinyintIndicates whether the job is enabled to be executed.
descriptionnvarchar(512)Description for the job.
start_step_idintID of the step in the job where execution should begin.
category_idintID of the job category.
owner_sidvarbinary(85)Security identifier number (SID) of the job owner.
notify_level_eventlogintBitmask indicating under what circumstances a notification event should be logged to the Microsoft Windows application log:

0 = Never

1 = When the job succeeds

2 = When the job fails

3 = Whenever the job completes (regardless of the job outcome)
notify_level_emailintBitmask indicating under what circumstances a notification e-mail should be sent when a job completes:

0 = Never

1 = When the job succeeds

2 = When the job fails

3 = Whenever the job completes (regardless of the job outcome)
notify_level_netsendintBitmask indicating under what circumstances a network message should be sent when a job completes:

0 = Never

1 = When the job succeeds

2 = When the job fails

3 = Whenever the job completes (regardless of the job outcome)
notify_level_pageintBitmask indicating under what circumstances a page should be sent when a job completes:

0 = Never

1 = When the job succeeds

2 = When the job fails

3 = Whenever the job completes (regardless of the job outcome)
notify_email_operator_idintE-mail name of the operator to notify.
notify_netsend_operator_idintID of the computer or user used when sending network messages.
notify_page_operator_idintID of the computer or user used when sending a page.
delete_levelintBitmask indicating under what circumstances the job should be deleted when a job completes:

0 = Never

1 = When the job succeeds

2 = When the job fails

3 = Whenever the job completes (regardless of the job outcome)
date_createddatetimeDate the job was created.
date_modifieddatetimeDate the job was last modified.
version_numberintVersion of the job.
Table.1 Result columns of dbo.sysjobs

Conclusion

In this simple article, we discussed where the SQL Jobs are stored. I assume you all found this article much useful. We will discuss more concepts in SQL in upcoming articles. Please share your feedback in the comment section.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Website Built with WordPress.com.

Up ↑

%d bloggers like this: