Cannot view Current Activity in EM

[Garyw]

Trying to view current activity in EM however get error 1222 Lock request time out period exceeded, this also occurs when trying to view tempdb database.

Is this a EM Gui error or locking issue and hwo do you resolve?

Thanks

GaryW

[WizKid]

See if any other process is causing a block or a deadlock. To see this, while EM is clocking, logon to QA and run sp_who.

[Garyw]

In the sql returned how do you determine wether its locked ?

Surely the current actiivty window is teh Gui way of doing sp_who?

Thanks

[WizKid]

The column BLK in the output of SP_WHO is defaulted to 0. If blocking is detected, the row for the blocked SPID (victim) contains the SPID of the blocker (culprit) in the BLK column.

EM does more than SP_WHO; more like SP_WHO2 which requires the use of temp_db database. Also, EM is much known to the use of cursor based operations which may cause/effect blocking.

[Garyw]

Many thanks you are indeed the wizkid, can I jsut kill the culprit.

[WizKid]

It's upto you.
If you have found the culprit SPID, you might want to do the following.

run : SELECT last_batch, * from master.dbo.sysprocesses (nolock) where spid = <<CULPRIT SPID>>
to see how long has this process been running and other details like user-id, workstation/host name etc.,.

run : DBCC INPUTBUFFER(<<CULPRIT SPID>>) to see what command is being run.

if it is OK to kill; KILL

HTH.,.