Press "Enter" to skip to content

Category: SQL Agent

Job Name Function

Jen McCown has written a function to simplify getting SQL Agent job names:

So yeah, now it takes fewer keystrokes to get the job name. I used to:

SELECT *
FROM msdb..sysjobschedules
WHERE job_id IN ( SELECT job_id
FROM msdb..sysjobs
WHERE name LIKE ‘CollectorDBFilePropertiesGet-%’ );

But now I:

SELECT *
FROM msdb..sysjobschedules
WHERE dbo.JobName(job_id) LIKE ‘CollectorDBFilePropertiesGet-%’ ;

Jen’s got the function available on her site.

Comments closed

Check Your Clocks

Thomas Rushton reminds us to check our clocks and our SQL Agent jobs:

At 1am the time jumps straight to 2am. Got any jobs scheduled to run at 01:30? They ain’t going to happen. I hope they weren’t important.

He also has the start of a script which helps fix timing issues, either from losing an hour in spring or gaining an hour in fall.  This is a timely reminder (no pun intended) that Daylight Savings Time begins on Sunday, March 13th this year in the US and March 27th in most of Europe.

Comments closed

Getting Running SQL Agent Jobs

Andy Mallon introduces us to xp_sqlagent_enum_jobs, tells us not to use it, and gives us an alternative:

If you landed on this post, I’m guessing you know the answer to this, so I’ll be quick. xp_sqlagent_enum_jobs is an undocumented (and thus, unsupported) extended procedure that gives information on whether a job is currently running, when it last ran, when it runs next, etc.

The supported alternative is to use sp_help_job (which calls xp_sqlagent_enum_jobs), but sp_help_job is difficult to use in automation because trying to do INSERT #temp EXEC sp_help_job will result in an error due to nested INSERT…EXEC statements. As a result, people often turn to calling the unsupported xp directly.

I never actually knew about this procedure; I always just wrote queries against the msdb tables.  Andy’s solution is more elegant than what I normally come up with, though.

Comments closed

SQL Agent Schedules

Kenneth Fisher talks SQL Server Agent schedules:

Because you can re-use the schedules you want to be careful naming them. I’ve seen far to many schedules named Schedule 1 or Job Name Schedule. The first is non-descriptive and the second too specific (it would look silly as the schedule for another job with a different name). Give your schedules names like Every Weekday at 4AM or The third of the month at 6AM. This way when you go to pick the right schedule you know which one is which.

Word of warning (which Kenneth also notes):  be careful about modifying those schedules; if you think you’re editing the schedule for just one job, you might actually be modifying schedules for a bunch of jobs.

Comments closed

Who Alerts When The Alerts Are Down?

Chris Bell uses Powershell to alert us when the SQL Agent service goes down:

Now that we have our PS1 file that will send the email alert, but we need to have the job run when the service fails. To set this open up services and right click on the SQLServerAgent service you wish to add the failure alert to and select properties. Navigate to the Recovery tab and set one of the failure actions to “Run a Program” I tend to choose it as the first failure action as I like to look at why my service failed before I just restart it. You can attempt to restart first and then run the program on if it fails again by setting the second failure to run a program. The choice is yours!

Very cool.

Comments closed

Finding Objects Using T-SQL

Derik Hammer shares a couple of snippets he uses to find objects and SQL Agent jobs.

Here’s one of my favorites, which searches for code within stored procedures, functions, and views:

SELECT
OBJECT_SCHEMA_NAME(sm.object_id) AS SchemaName,
OBJECT_NAME(sm.object_id) AS ObjectName,
CONCAT(OBJECT_SCHEMA_NAME(sm.object_id), '.', OBJECT_NAME(sm.object_id)) AS FullName,
CONCAT(OBJECT_SCHEMA_NAME(sm.object_id), '.', OBJECT_NAME(sm.object_id), ',') AS CommaSeparatedName,
definition
FROM sys.sql_modules sm
WHERE
sm.definition LIKE '%DEFINITION%'
--AND OBJECT_SCHEMA_NAME(sm.object_id) = 'Something'
--AND OBJECT_NAME(sm.object_id) = 'Something'
ORDER BY
SchemaName,
ObjectName;
Comments closed

SQL Agent Reporting

Mike Fal shows us how to use Powershell and T-SQL to get SQL Agent job status:

This is effective, but I struggle a little with the SQL query. It’s good, but suffers from the structure of the jobs tables in MSDB. We have to account for that and it makes the SQL query a little convoluted. It would be helpful if we could reference a simple data set like the Job Activity Monitor in SSMS.

Of course, this is a leading question on my part. There is a way to do this and it is by leveraging the SQL Server Management Objects (SMO). This .Net library is the API interface for working with SQL Server and is what SSMS is built on. Because it is a .Net library, we can also access it through Powershell.

SMO’s a powerful thing.

Comments closed