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;
GO
--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
GO

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.