SQL JOBS:Query to find last run status of scheduled Jobs
Here is a T-SQL query to find the last run status of all the scheduled jobs in SQL Server.
This query will be handy when you are not able to access the "Job Activity Monitor"
This query will be handy when you are not able to access the "Job Activity Monitor"
SELECT SUSER_NAME(members.role_principal_id) AS [ServerRole]
,logins.name AS 'RoleMember'
,'EXEC sp_addsrvrolemember ''' +logins.name+''', '''+
SUSER_NAME(members.role_principal_id)+'''' AS [Command to add role members]
FROM sys.server_role_members members, sys.server_principals logins
WHERE members.role_principal_id >=3 AND members.role_principal_id <=10 AND
members.member_principal_id = logins.principal_id
and logins.name <>'sa'
SQL JOB:SQL Query to find currently running jobs and duration
Here is a T-SQL query to find the currently executing jobs.
The output of this query will be the list of jobs that are currently running along with the number of seconds
it is been running.
SELECT J.name as Running_Jobs,
JA.Start_execution_date As Starting_time,
datediff(ss, JA.Start_execution_date,getdate()) as [Has_been_running(in Sec)]
FROM msdb.dbo.sysjobactivity JA
JOIN msdb.dbo.sysjobs J
ON J.job_id=JA.job_id
WHERE job_history_id is null
AND start_execution_date is NOT NULL
ORDER BY start_execution_date
