Solved

Identify when a connection was lost

Posted on 2012-03-20
5
255 Views
Last Modified: 2012-03-23
Hi,

I have a SQL Server box and when executing sql statements it usually returns with data but every great once in a while I am getting a connection timeout error.  Is there a way to find out when a network connection was lost and/or restablished?  SQL is running on Windows Server 2008.

thank you
0
Comment
Question by:yechan
  • 2
  • 2
5 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 37744437
"I have a SQL Server box and when executing sql statements it usually returns with data but every great once in a while I am getting a connection timeout error. "

Where is the timeout coming from?? SQL code object or outside sql app code?
You could change the SQL query timeout from default 600 seconds in 2005/2008 to 0 or unlimited but CAUTION don't just do that in a production SQL server. This way the queries will never timeout in SQL but connections may be still chopped from the app - connection timeouts or other defaults.

"Is there a way to find out when a network connection was lost and/or restablished?"
Yes, you can use SQL profiler for that but CAUTION again because if you just start a default trace AS IS against a busy production SQL server you may bring it down.
0
 
LVL 1

Author Comment

by:yechan
ID: 37746657
hi lcohan,

The timeouts are a result of running unit tests on a bamboo server.  The database itself is not very transactional and also it will be run against our test database, thus, not too worried about bringing down SQL Server.  Do you know how to configure profiler so that it captures only lost/restablished network connections?
0
 
LVL 38

Accepted Solution

by:
ChiefIT earned 500 total points
ID: 37755756
On a 2008 server, go to the start button and on the run line type "reliability". The reliability monitor will show you up/down times and most likely the cause of them.
0
 
LVL 1

Author Comment

by:yechan
ID: 37756533
Thank you chief.  This is a really cool tool.
0
 
LVL 38

Expert Comment

by:ChiefIT
ID: 37757502
WIN 7 ALSO OFFERS THAT TOOL... SO YOU CAN USE IT TO TROUBLESHOOT YOUR W7 DESKTOP COMPUTERS.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now