Solved

How To Troubleshoot SQL Server 2005 Timeout Issues

Posted on 2007-11-15
6
9,171 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org Go to that link and select download selenium in the right hand columnThat will then direct you to their download page.From that page s…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

820 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