Here i am writing a simple query to find the list of all Jobs currently running on SQL Server. We can write below simple query to find the "sysjobs" on each server.
Example:-
Query 1: For Return All Jobs:
SELECT job.job_id, notify_level_email, name, enabled, description, step_name, command, server, database_name FROM msdb.dbo.sysjobs job INNER JOIN msdb.dbo.sysjobsteps steps ON job.job_id = steps.job_id;
Query 2: For Return All Enabled Jobs:
SELECT job.job_id, notify_level_email, name, enabled, description, step_name, command, server, database_name FROM msdb.dbo.sysjobs job INNER JOIN msdb.dbo.sysjobsteps steps ON job.job_id = steps.job_id WHERE job.enabled = 1;
Query 3: Now the query to gets the category name and filters out the report server jobs:
SELECT sysjobs.name 'Job Name', syscategories.name 'Category', CASE [description] WHEN 'No Description available.' THEN '' ELSE [description] END AS 'Description' FROM msdb.dbo.sysjobs INNER JOIN msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id WHERE syscategories.name <> 'Report Server' ORDER BY sysjobs.name;
Example:-
SELECT job_id, [name] FROM msdb.dbo.sysjobs;
Below query for list of all the jobs currently running on server:Query 1: For Return All Jobs:
SELECT job.job_id, notify_level_email, name, enabled, description, step_name, command, server, database_name FROM msdb.dbo.sysjobs job INNER JOIN msdb.dbo.sysjobsteps steps ON job.job_id = steps.job_id;
Query 2: For Return All Enabled Jobs:
SELECT job.job_id, notify_level_email, name, enabled, description, step_name, command, server, database_name FROM msdb.dbo.sysjobs job INNER JOIN msdb.dbo.sysjobsteps steps ON job.job_id = steps.job_id WHERE job.enabled = 1;
Query 3: Now the query to gets the category name and filters out the report server jobs:
SELECT sysjobs.name 'Job Name', syscategories.name 'Category', CASE [description] WHEN 'No Description available.' THEN '' ELSE [description] END AS 'Description' FROM msdb.dbo.sysjobs INNER JOIN msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id WHERE syscategories.name <> 'Report Server' ORDER BY sysjobs.name;
No comments:
Post a Comment