SQL Query to know if a job is running or Not

1. Below query will show list of all running jobs.

SELECT
     job.Name,
     job.job_ID,
     job.Originating_Server,
     activity.run_requested_Date,
     datediff(minute, activity.run_requested_Date, getdate()) as Elapsed
FROM msdb.dbo.sysjobs_view job
INNER JOIN msdb.dbo.sysjobactivity activity
ON (job.job_id = activity.job_id)
WHERE run_Requested_date is not null and stop_execution_date is null
AND job.name IN ('<YOUR JOB NAME>')



2. Query below will keep on running unless the specified job is running.

WHILE
(
    EXISTS
    (
       SELECT job.Name
       FROM msdb.dbo.sysjobs_view job
       INNER JOIN msdb.dbo.sysjobactivity activity
       ON (job.job_id = activity.job_id)
       WHERE run_Requested_date IS NOT NULL AND stop_execution_date IS NULL
       AND job.name IN ('<Your Job Name>')
    )
)
BEGIN
 SELECT 0
END 

This can be very useful to create dependency with SSIS package.