SPID Of Running Job

[gharabed]

I want to trace a job that is running through sql profiler. How can I determine the SPID of a particular job that is running?

 

[Catadmin]

I'm not sure there's a way to do this through Profiler. You can run SP_WHO2 or SP_WHO2 Active on QA/SSMS query window to see what processes are running. Or try using the @@SPID function, though I've never used it.

DBCC Input_Buffer(<spid>) will give you the command that's running too.

AFAIK, SPIDs are not numbers stored anywhere in the server. They are process IDs, relevant to the thread connection a job or user is using.

I'm happy to be proven wrong, of course, but that's what I always thought of them as.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

You can look at the sysprocesses table. In the program_name column it will say SQLAgent - Job Step then a binary value. That should be a binary version of the job_id from the msdb.dbo.sysjobs.

Keep in mind that each step of a job can have a different spid. Your best bet will be to run a trace based on the NT Login of the login with the SQL Agent is running under.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com