During my
journey with SQL Server, I used to query the SQL Server agent jobs metadata, by
default it is very important to get some information related to the jobs
defined on your SQL instance, the current running ones, some details like steps
defined per job, … etc.
Due to all
the above situations, I used to design a lot of complex queries that related to
SQL jobs and thought that I am doing the right job … but unfortunately I am
not.
The secret
behind this is simply the system stored procedure sp_help_job which literally save a lot of time and effort
of creating such complex long queries, the sp_help_job
is
a system stored procedure located in msdb system database and has a set of
parameters that cover most of the information required for SQL agent jobs.
We can use sp_help_job stored procedure in the below scenarios:
1-
Getting all jobs on a SQL
instance with some metadata like server, job name, owner, notification methods,
create date and last run date … this can be done by executing the stored
procedure without specifying any parameters.
exec msdb.dbo.sp_help_job;
2-
Getting all current running
jobs, this can be done by specifying the parameter @execution_status with value
1.
exec msdb.dbo.sp_help_job @execution_status=1;
3-
Getting details for a
specific job, by using parameter @job_name and specifying the job name we can
get information about the job itself, the jobs steps, the job schedule and the
job outcome.
exec msdb.dbo.sp_help_job @job_name=[Job Name];
4-
Getting the jobs owned by a
specific login, by specifying parameter @owner_login_name.
exec msdb.dbo.sp_help_job @owner_login_name=[Login Name];
5-
Getting the enabled or
disabled jobs on the instance by specifying parameter @enabled with 0 value for
disabled jobs and 1 for the enabled ones.
exec msdb.dbo.sp_help_job @enabled=0;
exec msdb.dbo.sp_help_job @enabled=1;
There are
some other parameters that can be specified like the subsystem name, the category
name and date created/ modified.
The below
snapshot shows all available parameters for sp_help_job
stored
procedure.
I really love your blog there's a lot to share. Keep it up.Visit my site too.
ReplyDeleten8fan.net
www.n8fan.net
You can pick up tips from the likes of expert magicians like David Blaine and Criss Angel when you watch them perform first hand. los angeles magician
ReplyDeleteThis post is really awesome. Genuinely i like this blog. It gives me more useful information. I hope you share lots of things with us . jobs for people with disabilities
ReplyDelete