Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

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

Nov 29 2012 12:00AM by Nakul Vachhrajani   

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.

Tags: #SQLServer, SQL Server, Administration, DBA, Tips, HowTo

Nakul Vachhrajani
4 · 36% · 11648


Your Comment

Sign Up or Login to post a comment.

"#0217-SQL Server-Script-Identify SQL Server Agent jobs no longer targeted to a database" rated 5 out of 5 by 5 readers
#0217-SQL Server-Script-Identify SQL Server Agent jobs no longer targeted to a database , 5.0 out of 5 based on 5 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]