How to Read sp_who2

Question

How to Read sp_who2

John_Idol

Ten Centuries

Points: 1011
More actions
May 16, 2007 at 5:01 am

#120020

Hi All,
I got problems with some procedure whic are blocking one of my databases. Biggest problem is I don't which those procedures are.
Last time it happened I ran sp_who2 active. I was able to identify two suspended blocked processes, one (UPDATE - SPID:58) blocked by the other one (SELECT - SPID: 68), the other one blocked by a runnable one (UNKNOWN TOKEN - SPID: 70).
For all of them I got SQLAgent - TSQL JobStep (Job [0xDA9A9D92A78C0E42A37EE96C7BEBB073] : Step) so it's pretty obvious they are executed by SQL Agent Jobs.
I am trying now to identify the jobs given the keys 0xDA9A9D92A78C0E42A37EE96C7BEBB073 but no idea how to do it.
I was looking at the SPIDs but I discovered they can change, so no use in that I think.
Anyone of you guys can help?
Cheers and thanks in advance!
Giovanni
JI

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Zubeyir SSCrazy Points: 2559 More actions · Answer 1

select * from dbo.sysjobsteps where job_id = 0xDA9A9D92A78C0E42A37EE96C7BEBB073

Should just work.

bledu SSChampion Points: 12335 More actions · Answer 2

you first have to convert [0xDA9A9D92A78C0E42A37EE96C7BEBB073] to varchar because its a hex string.

Everything you can imagine is real.

John_Idol Ten Centuries Points: 1011 More actions · Answer 3

John_Idol

Ten Centuries

Points: 1011

May 16, 2007 at 6:15 am

#706739

Perfect!

Many Thanks for your help guys,

G

JI

John_Idol Ten Centuries Points: 1011 More actions · Answer 4

it worked:

job_id: 929D9ADA-8CA7-420E-A37E-E96C7BEBB073

step_name: Full-Text Indexing

command: use [HumanResources] exec sp_fulltext_table N'[dbo].[tbl_People_Attachments]', N'start_full'

Can't find job ID on job's properties. How can I understand if this is one of my jobs of a system one?

Thanks!

G

JI

Zubeyir SSCrazy Points: 2559 More actions · Answer 5

Initially there is no system job on any sql instance. But services like full text or log shipping can produce some jobs on the server. You can identify those by looking either their names or their categories

John_Idol Ten Centuries Points: 1011 More actions · Answer 6

Ok,

I discovered i have a full-text indexing active on one of my tables.

It seems while this 'job' is running if another job or process runs the DB is beign blocked.

There's actually not schedule set up for the full-text indexing, so I can't understand when it runs. Should I set up a schedule in order to fix a temporal range (probably during the night) for the indexing to run?

Thanks!

G

JI

Zubeyir SSCrazy Points: 2559 More actions · Answer 7

The reason that it runs automatically is "Auto Track Changes". This option seems to be on on your FT index. You can set a job to run during night.

Zubeyir

John_Idol Ten Centuries Points: 1011 More actions · Answer 8

OK,

I set the track change property to Off and setup a schedule to run the indexing at night.

Thanks again!

G

JI

Zubeyir SSCrazy Points: 2559 More actions · Answer 9

Zubeyir

SSCrazy

Points: 2559

May 16, 2007 at 11:09 am

#706887

You're welcome