Tuesday, November 29, 2016

Check For SQL Agent Jobs For A Dropped Database


Check For SQL Agent Jobs For A Dropped 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:
01.USE msdb;
02.GO
03.--Query to identify orphaned jobs!
04.SELECT sj.database_name AS OriginalTargetDBName,
05.sj.job_id AS JobId,
06.sj.step_id AS JobStepId,
07.sj.step_name AS StepName,
08.sj.subsystem AS SubSystem,
09.sj.command AS JobStepCommand,
10.sj.last_run_date AS LastExecutionDate
11.FROM msdb.dbo.sysjobsteps AS sj
12.WHERE DB_ID(sj.database_name) IS NULL AND   --If the database no longer exists,
13.--DB_ID() would return NULL
14.sj.database_name IS NOT NULL
15.GO
Trust me when I tell you that these 15 lines of code can save you hours of time.

Get The Best Deals On All Things Tech From DealExtreme!

No comments:

Post a Comment