How to trace connection leak

How to trace connection leak

 

Problem description: (http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/10/13/connection-leak-its-symptoms-and-troubleshooting.aspx)

Connection leak basically happens when we open a connection to the database from our application and forget to close it, or for some reasons it doesn’t get closed.

 

 

 

Errors related to Connection leak (http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/10/13/connection-leak-its-symptoms-and-troubleshooting.aspx)

 

1) Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
2) SQL Server Does Not Exist Or Access Denied
3) General Network Error

 

 

 

 

Problem confirm:

 

For .Net 2.0 and above - Use performance counter : .Net data provider for sqlserver -> NumberofreclaimedConnection

For .Net 1.x - http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/10/13/connection-leak-its-symptoms-and-troubleshooting.aspx

 

Resolution 1: SQL Profiler

 

Audit Login Event

DatabaseName

LoginSid :Each SID is unique for each login in the server.

EventSubClass: check connection is pooled or not

 

Audit Logout Event

LoginSid :Each SID is unique for each login in the server.

 

RPC:Completed Event(The RPC:Completed event class indicates that a remote procedure call has been completed.)

LoginSid :Each SID is unique for each login in the server.

TextData :Text of the stored procedure call.

 

Step to trace:

  1. Setup/turn on sql profiler
  2. Run regression/stress test
  3. Stop sql profiler
  4. Retrive sql profiler report
  5. Find out what is the isolate loginsid (only login but not logout)
  6. Find out the sql text base on loginsid & Textdata

Resolution 2: C# sqlconnection event - connection.StateChange and System.Diagnostics.StackTrace

 

CODE refer to http://www.codeproject.com/KB/database/connectionmonitor.aspx

 

 

 

 

Step to trace:

  1. Add StateChange Event to SqlConnection
  2. DoOpen():If connection.open then record the time, connection hashcode, getstacktrace => log
  3. DoClose():If connection.close then record the time, connection hashcode => log
  4. Run regresstion/stress test
  5. Find out what connection lose close

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
HikariCP是一个高性能的数据库连接池,当出现"Apparent connection leak detected"(检测到明显的连接泄漏)的错误时,这通常意味着你的应用程序没有正确地释放数据库连接,导致连接池中的连接没有被归还。 出现这个错误的原因可能有多种可能性,以下是一些常见的问题和解决方法: 1. 确保你在使用完数据库连接后,调用了`connection.close()`方法将连接归还给连接池。最好在`finally`块中进行连接的释放,以确保无论是否发生异常,连接都能正确地关闭。 2. 确保你在使用完数据库连接后,调用了相关资源(如`Statement`或`ResultSet`)的关闭方法。如果你没有正确关闭这些资源,连接可能不会被释放。 3. 确保你在使用完数据库连接后,将连接返回到连接池。如果你手动管理连接,确保调用连接池的`connection.close()`方法来关闭连接。 4. 检查你的代码是否存在长时间持有数据库连接的情况。长时间占用连接可能导致连接池中的连接不足,并导致连接泄漏。 5. 检查数据库操作过程中是否发生了未捕获的异常。如果发生异常,确保在异常处理中正确地关闭连接。 6. 调整连接池的配置参数,如`maximumPoolSize`(最大连接数)和`idleTimeout`(连接的空闲超时时间),以适应你的应用程序需求。 通过仔细检查你的代码,确保在使用完数据库连接后正确地释放连接,可以帮助解决连接泄漏的问题。如果问题仍然存在,请提供更多的错误信息和相关代码,以便我能够提供更具体的帮助。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值