Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

sysjobs System Table - SQL Server 2000

RSS
Modified on Fri, Feb 27, 2015, 8:24 AM by Administrator Categorized as SQL Server System Tables
Stores the information for each scheduled job to be executed by SQL Server Agent. This table is stored in the msdb database.

Column nameData typeDescription
job_iduniqueidentifierUnique ID of the job.
originating_servernvarchar(30)Name 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)System identification number (SID) of the job owner.
notify_level_eventlogintBitmask indicating under what circumstances a notification event should be logged to the Microsoft® Windows NT® 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.

Sample Query

select 
     JobName = job.name
    ,s.database_name
    ,s.command    
    ,job.job_id
    ,s.step_id
    ,s.step_name
    ,s.subsystem
from 
    msdb.dbo.sysjobs job
    left join msdb.dbo.sysjobsteps s
        on job.job_id = s.job_id

where 1=1
    and job.name like 'My System%'
order by
     job.name
    ,s.step_id

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.