CODEWITHSUNDEEP
sql-serversmosql-server-agent
automatic
automatic

Reputation: 2737

How to find SPID of a executing SqlAgent job using SMO

With SMO objects using Server.JobServer.jobs to get a list of jobs, I can find the status of each job. For those that are currently executing I would like to find the SPID it is executing on. I can also get a list of the server's processes using Server.EnumProcesses(). This gives me a list of currently active SPIDs. I want to match the two.

The best I've been able to come up with is to convert the jobid to a string and substring the jobId out of the program string in the EnumProcesses table (which, at least on my system, embeds the jobId in this description). It's really ugly for a couple of reasons not the least of which is that the Guid in the program description and the guid for jobID have their bytes switched in the first 3 pieces of the string representation. Yuck.

Is there a better way to do that using SMO?

Upvotes: 1

Views: 6108

Answers (2)

Nithya
Nithya

Reputation: 11

Use SP_who2 , in the result "Program" field you will find SQLAgent-Jobid. you will get the jobID from sysjobs from MSDB table. now you can get the sp_id of the job

Upvotes: 1

mrdenny
mrdenny

Reputation: 5078

Using SMO, not that I know of.

I have done this using T/SQL. Take the job_id value and cast it to varbinary. That should then match the value in the application name (after the "SQL Agent" part).

Upvotes: 1

Related Questions