Solved

How To Troubleshoot SQL Server 2005 Timeout Issues

Posted on 2007-11-15
6
9,170 Views
Last Modified: 2013-11-07
I'm developing a .Net 2.0 application and have run in to a bit of a problem.  My application runs great for an hour or two, but eventually, I start getting Timeout errors when trying to execute SQL commands.  It's not every type of SQL either - just the Add, Update, Delete type activity...selects work well.

Anyway, what I really want to know is how can I monitor connections to my SQL 2005 database?  I have a feeling something in my application is opening up resources and not closing them and eventually my SQL Server just stops responding.

I'd be happy to share all my code, but there's lots of it, and what I'm really looking for is a technique, not problem solving specific to this issue.

0
Comment
Question by:Steve Krile
6 Comments
 
LVL 6

Expert Comment

by:Marcel Hopman
ID: 20292421
Are all transactions comitted ? You can have a build up of locks, maybe even deadlocks. Use the profiler to monitor your locks to see if they are the problem...
0
 
LVL 10

Expert Comment

by:pai_prasad
ID: 20292965
sp_who2 gives the list of sessions opened..
sp_who2 'active' gives list of active sessions

check BlkBy col to find out if some session is blocking other causing timeout.

in 2005
Select *
from sys.dm_exec_sessions

check the program name ...
to begin with...

Prasad
0
 
LVL 16

Author Comment

by:Steve Krile
ID: 20293471
OK, and forgive my ignorance...but what the heck am I looking for?  If I run the SP sp_who2, I get a result, but what does it mean?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 13

Accepted Solution

by:
Wizilling earned 500 total points
ID: 20293636
Start a trace in SQL Profiler. (might have to readup a little) (the results can be saved to a table )
main objects to capture are RFC Starting, RFC Completed, BATCH starting Batch completed.
Filter it according to your database.
Have columns like Duration, Text, Cpu, Reads, Writes, Start Time, End time.

start the profiler. run your applicaiton , test procedures.. after u think u have collected enough data, analyse your trace. Look for queries that take long to complete. or have a high CPU or high reads or writes.
Once you try to tune your long running queries your applicaiotn will run much faster.

Also try to do a re-index and a update statistics on your database too.

0
 
LVL 6

Expert Comment

by:Marcel Hopman
ID: 20293688
I will no give you very much info, it just gives you the connections connected to SQL Server.
Beter to use your profiler or (its easier) to look at the locks in the Activity Monitor of SS Management Studio. Start looking when things are slowing down...
0
 
LVL 16

Author Comment

by:Steve Krile
ID: 20297712
Thanks Wiz.  I'm off to the races (and hitting the books!).  It's tough when you don't even know what question to ask  :)

Starting with your advice, I also found this link:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20521525.html

I've already plugged two giant "abusers" that were firing every time my pages loaded.  Not sure what the problem is yet, but I'm on my way to a more efficient application.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Here is a helpful source code for C++ Builder programmers that allows you to manage and manipulate HTML content from C++ code, while also handling HTML events like onclick, onmouseover, ... Some objects defined and used in this source include: …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question