Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
John_Idol
Starting Member
39 Posts |
 Posted - 2007-05-11 : 12:10:58
|
| 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 [1]) so it's pretty obvious they are executed by SQL Agent Jobs. I am trying now to identify the jobs from 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.Anyone of you guys can help?Cheers and thanks in advance!Giovanni |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-11 : 12:20:03
|
| use profiler and it's Lock:Deadlock Chain Event Classto see what happens next time._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-11 : 12:35:07
|
| Try adding this to the master database and running it to see the commands that are executed.I'm in the middle of an article which includes this and also investigating the plans for everything that is running.http://www.nigelrivett.net/SQLAdmin/sp_nrInfo.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
John_Idol
Starting Member
39 Posts |
Posted - 2007-05-14 : 04:37:52
|
| Thanks all for your answers.spirit1, I don't understand what you are suggesting (I am new to DBA stuff...).nr, thanks for that!Do you guys think there's something I can do before it happens again with the information I have from the last time it happened?Cheers,Giovanni |
|
|
|
|
|
|
Identifiying Procedures from SPID