#JustLearned : Checking the Job Schedule status of a SQL job

We check daily and weekly jobs schedule status that has been scheduled in SQL server in many servers for different projects. I wrote some queries to get the status as below.(if you have better solution please comment)

We can use a readymade solution SPHELP_JOBSCHEDULE store procedure which takes jobid, jobname, schedulename, scheduleid and schedule descirption. But this will return multiple columns and what if we have to query only the enabled status of a schedule.

So the other approach may be to query the MSDB table DBO.SYSSCHEDULES which will give us the ‘enabled’ column from which we can get the Schedule status of the job. For more information we can join the dbo.sysjobschedules and dbo.SYSJOBS.

SELECT enabled as JobScheuduledStatus FROM DBO.SYSSCHEDULES WHERE [NAME]=’JSCH_HR600d’

*this one is already published in BeyondRelational.com

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s