List all the disabled and currently Running Sql Agent JOBS
This T-SQL would get you the Sql server Jobs that are disabled and running under the Sql Server Agent.
SQL server Jobs that are disabled and running under the SQL Server Agent
Introduction:
This TSQL script would help you to list the inactive or disabled Sql Agent JOBS in any Sql Server Instance.Objects Used for TSQL:
The TSQL script uses the sysjobs object in the MSDB(system) database to list all the Active and inactive SQL Agent jobs.TSQL to List all Inactive JOBS
SELECT job.Job_id AS [Job ID]
,notify_level_email [Notification Email Level]
,Name AS [Job Name],enabled,description
,Step_name AS [Step Name]
,command AS [Sql Command used]
,server AS [Server Name]
,Database_name AS [Database Name]
FROM msdb.dbo.sysjobs job JOIN
msdb.dbo.sysjobsteps step
ON job.job_id = step.job_id
WHERE job.enabled = 0TSql to list of currently running and Active Jobs
SELECT job.Job_id AS [Job ID]
,notify_level_email [Notification Email Level]
,Name AS [Job Name],enabled,description
,Step_name AS [Step Name]
,command AS [Sql Command used]
,server AS [Server Name]
,Database_name AS [Database Name]
FROM msdb.dbo.sysjobs job JOIN
msdb.dbo.sysjobsteps step
ON job.job_id = step.job_id
WHERE job.enabled = 1TSQL to get the List of Active and Inactive Jobs with the status field as Active and Inactive.
SELECT job.Job_id AS [Job ID]
,notify_level_email [Notification Email Level]
,Name AS [Job Name],enabled,description
,Step_name AS [Step Name]
,command AS [Sql Command used]
,server AS [Server Name]
,Database_name AS [Database Name]
, CASE WHEN job.enabled = 1 THEN 'Active' ELSE 'InActive' END AS [Job Status]
FROM msdb.dbo.sysjobs job JOIN
msdb.dbo.sysjobsteps step
ON job.job_id = step.job_idConclusion:
Using the above TSQL Queries you can easily find out the Active and inactive Jobs in your Sql Server Instance.