Solved

How To Troubleshoot SQL Server 2005 Timeout Issues

Posted on 2007-11-15
6
9,173 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

617 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