Solved

Identify when a connection was lost

Posted on 2012-03-20
5
260 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
[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
  • 2
  • 2
5 Comments
 
LVL 40

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

Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

730 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