| Author |
Topic |
|
albertkohl
Aged Yak Warrior
740 Posts |
 Posted - 2012-07-12 : 19:46:44
|
| so if i run SP_who2, nothing's blocked, no open transactions, but none of my query's are finishing. how can i find what's blocking? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-12 : 20:14:00
|
| what does this return?SELECT @@TRANCOUNT------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2012-07-12 : 23:17:12
|
| i get 0 :( |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-07-12 : 23:25:52
|
| Just for extra measure try:dbcc opentranBe One with the OptimizerTG |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-07-12 : 23:28:36
|
| Is your server in the middle of performing a maintenance routine? Or some job running causing a lot of contention? Is this server just a sql server or are you having contention with some non-sql thing? Does it respond to something really simple like: select getdate() ?Be One with the OptimizerTG |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2012-07-13 : 10:54:12
|
| dbcc opentran came up empty, simple query's will work, but with a little delay (like a few seconds). i know something is creating contention, but that's what i'm trying to track down. i killed everything, and started running things one-by-one and it was fine after that. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-07-13 : 10:59:22
|
| Cool - thanks for the update.Be One with the OptimizerTG |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2012-07-13 : 12:00:50
|
| is there any way to track down what it is when that happens though? 0 open trans, 0 blockers, what would you look for after that? (fyi, most of my dbs run read_committed_snapshot isolation too) |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-07-13 : 12:14:07
|
| May or may not be sql related. I would look at general performance indicators (via performance monitor and task manager) - see what processes are running, how active the disk is, how much memory is being used, how much processor, etc... You may get some clues based on the trouble areas as to what is causing it.Be One with the OptimizerTG |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Locking Issue?