SQL Server : SQL Job Status

-- SQL Server check job status with all job related information
USE msdb
EXEC dbo.sp_help_job @job_name =  N'TestSQLJob' ,
                               @job_aspect = N'ALL' ;
GO

-- Check run status of a job - check if job currently running
-- SQL Server Agent - Running = 1 means currently executing
DECLARE @job_id uniqueidentifier
SELECT @job_id=job_id FROM msdb.dbo.sysjobs WHERE name=N'TestSQLJob'

EXEC master.dbo.xp_sqlagent_enum_jobs 1, sa, @job_id
GO

-- SQL Server start job programmatically
EXEC msdb.dbo.sp_start_job  N'TestSQLJob' ;
GO