In initial days I had to scratch my head for a long time to find out the SQL Agent Job Name in Execution just to provide details to customer or to find out the job causing issues.
The below simple query will be helpful in the following situations:
- Several agent jobs showing in “sp_who2 active” results set and you want to identify them.
- You have identified the root blocker as a SQL Agent job and need the problem job name to notify Customer.
- Customer requested to find out the status of a job in progress etc.
sp_who2 active output does not show the job name:
The below simple query will help you find the information:
SELECT * FROM msdb.dbo.sysjobs WHERE job_id=CAST(<jobid> AS UNIQUEIDENTIFIER)
Considering the above example:
SELECT * FROM msdb.dbo.sysjobs WHERE job_id=CAST(0xD81B43320D3F2843B9A6A40CA120F14B AS UNIQUEIDENTIFIER)
Let me know if it helps and any suggestions are always welcome.
You may also find the below scripts useful:
List all permissions for a user in all or selective databases
Script to retrieve database backup information
Script to get SQL Cluster Failover Time and Node Name
Script to List All Users with db_owner role in SQL 2000
