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 |
|
BigSam
Starting Member
30 Posts |
 Posted - 2006-03-21 : 10:34:50
|
| Occasionally one of the update statements in our asp application times-out. To get around the problem we run a very simple update on any table & the problem seems to go away (sometimes it takes multiple attempts to get these to run). We cannot reproduce the situation in the lab, because we don't know what causes the transactions to hang.My first thought was a deadlock, but I haven't been able to see one when the transactions are hanging. This could be because I haven't been able to connect to the server quickly enough when the situation arises.I did run Profiler when we were running a transaction to fix the problem. In the trace I saw a loop with 'Scan: Started, Lock: Acquired, Scan: Stopped, Lock: Released' before the transaction finally completed.I still think this is related to a deadlock, but really don't know where or how to investigate any further. Any advice would be helpful.Thanks,BigSam |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-03-21 : 14:28:03
|
| when you say that you have not been able to connect to the server quickly enough, what does that mean? Are you trying to look for blocking using enterprise manager? Use Query analyzer instead and simply run sp_who2 and look for SPIDs that are blocking other SPIDs.if you do see blocking occuring with sp_who2, the easiest way to see what is going on is to use this script http://support.microsoft.com/kb/271509/Use the code from that article that runs the blocker script in a loop so that it continually collects data. You can then use that output file with the sherlock tool (http://www.sqlteam.com/item.asp?ItemID=21737) to help analyze the blocking problem. Of course, you need to run this script when the blocking problem is occuring. Try not to run the script for too long a time, becuase the output file will take a long time to process with sherlock.-ec |
 |
|
|
BigSam
Starting Member
30 Posts |
Posted - 2006-03-21 : 14:41:23
|
| Thanks ec.My slow connection time involves going through a slugish VPN & the signing on at the remote server. - I'm working on a work around for that now.I wasn't familiar with the Sherlock tool. I'll check it out. Also, thanks for the kb article.BigSam |
|
|
|
|
|
|
|
|
Update timeout