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.

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.

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.

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;

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.

Categories

July 2017
MTWTFSS
« Jun  
 12
3456789
10111213141516
17181920212223
24252627282930
31