Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9180
  • Last Modified:

How To Troubleshoot SQL Server 2005 Timeout Issues

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
Steve Krile
Asked:
Steve Krile
1 Solution
 
Marcel HopmanCommented:
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
 
pai_prasadCommented:
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
 
Steve KrileAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
WizillingCommented:
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
 
Marcel HopmanCommented:
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
 
Steve KrileAuthor Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now