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 |
|
sigpop
Starting Member
6 Posts |
 Posted - 2007-06-20 : 16:27:19
|
| I have an ASP.NET website connecting to a Sql Svr 2005 db. Periodically I'll see an error in my logs indicating a timeout when calling a specific sp:"ERROR in [ProgNotSet].clsDataLayer.DbExecute = 5: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. SQL = spm_...". This is a simple stored proc that does an update on two fields in one table.At a later time when I notice the error, I can run the exact sql script in SSMS (Mgmt Studio) and it works fine. This system has a lot of database calls. I assume that some other sp/call is locking the table used in the failing stored proc. Seem feasible?I started a trace in Profiler that logs just two events: Locks: Deadlock and Locks: Deadlock Chain. Of course I have to get "lucky" and have the problem occur when my trace is running. Is this a good approach for my trace? How can I verify this trace will work? I tried opening two query windows and started a transaction in one window but did not close it (no commit or rollback). In the other window I did a simple SELECT *. Of course the statement does not complete whilst my transaction is open. After ten secs I close the transaction and both windows complete, but there are no entries in my trace.Perhaps a better approach is to just log all queries/sp that access my table? Can I do that (easily) in Profiler? I see the filters but given the traffic on this database I am anxious about using them to look for the table name.Thx for any suggestions for troubleshooting. |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-06-21 : 04:29:27
|
| Are you specifically programming the locks on the tables in other procedures/queries, or are you just assuming the tables are locked because queries are running at the same time causing your procedure to fail?My guess is the issue is probably not the table locking, but rather it has to do with the remote connection to the server. I would spend a little more time to try to duplicate the extended lag time when running the procedure locally on sql. If you can't duplicate locally, then there is a good chance it's not a table locking error. Verify that the remote machine is not possibly temparaly having connection issues with the network due to Network congestion (I have seen this happen in enviorments caused by numourus things (high network traffic, Cheap switches, poorly configured servers (I.e. Have 1 server handling too many things (DHCP/DNS/Domain Controller/File Server/etc all on one box and 1 10/100 NIC )Unfortuantly there is no easy way I know of to pin-point this besides a little bit of trial and error.Hope this helps, I don't envy you in figuring out this one. |
 |
|
|
sigpop
Starting Member
6 Posts |
Posted - 2007-06-21 : 07:41:47
|
Thx for the reply, Vinnie.quote:
Originally posted by Vinnie881
Are you specifically programming the locks on the tables in other procedures/queries, or are you just assuming the tables are locked because queries are running at the same time causing your procedure to fail?
I'm not manually setting any locks. In fact I have some NOLOCK hints in place on some of the more taxing queries that use that table.quote:
My guess is the issue is probably not the table locking, but rather it has to do with the remote connection to the server. I would spend a little more time to try to duplicate the extended lag time when running the procedure locally on sql. If you can't duplicate locally, then there is a good chance it's not a table locking error. Verify that the remote machine is not possibly temparaly having connection issues with the network due to Network congestion (I have seen this happen in enviorments caused by numourus things (high network traffic, Cheap switches, poorly configured servers (I.e. Have 1 server handling too many things (DHCP/DNS/Domain Controller/File Server/etc all on one box and 1 10/100 NIC )
I'm pretty sure the network is solid. With the amount of traffic between the webserver and db server I would see a ton of alarms/errors if it wasn't. Sql Svr runs on it's own box as does the web server. |
|
|
|
|
|
|
|
|
sporadic db timeout, assume due to locking?