#0217-SQL Server-Script-Identify SQL Server Agent jobs no longer targeted to a database

Today’s post is a short one – a quick tip/script that would come to use in most development/quality assurance environments.

Most non-production environments see a constant change to the number and nature of the databases that are deployed on a given SQL Server instance. There would be a copy of the database for maintenance teams, another one for the team working on newer enhancements to a product and a few other copies for special purposes, targeted to study and resolve a specific issue or deployment. Once the particular task is completed, these databases are dropped. What is left behind are the components of that deployment that are not directly “contained” (for lack of a better word) by SQL Server.

One of many such “non-contained” components are the SQL Server Agent jobs. When a database is dropped, the jobs remain active in the SQL Server Agent and if a schedule is associated to the job, failures would be reported whenever the jobs are automatically executed. As part of the standard process that I follow whenever I drop a database, I run the following script to identify SQL Server Agent jobs which are no longer targeted to any database:

USE msdb;
--Query to identify orphaned jobs!
SELECT sj.database_name AS OriginalTargetDBName,
       sj.job_id AS JobId,
       sj.step_id AS JobStepId,
       sj.step_name AS StepName,
       sj.subsystem AS SubSystem,
       sj.command AS JobStepCommand,
       sj.last_run_date AS LastExecutionDate
FROM msdb.dbo.sysjobsteps AS sj
WHERE DB_ID(sj.database_name) IS NULL AND   --If the database no longer exists,
                                            --DB_ID() would return NULL
      sj.database_name IS NOT NULL

I trust that you have found (or will find) the above query useful. If you use any other query/mechanism or have faced similar issues with cleanup of other objects related to the SQL Server Agent, I would like to know. Do share your experiences in the comments below.

Until we meet next time,

Be courteous. Drive responsibly.


Let me know what you think about this post by leaving your feedback here!

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