Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How To Troubleshoot SQL Server 2005 Timeout Issues

Posted on 2007-11-15
6
Medium Priority
?
9,175 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 13

Accepted Solution

by:
Wizilling earned 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

715 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