Solved

Identify when a connection was lost

Posted on 2012-03-20
5
262 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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 ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

696 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