 | |
Error: Connection is busy with results for another command
microsoft.public.sqlserver.clients microsoft.public.sqlserver.clients
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Error: Connection is busy with results for another command -
02-15-2010
, 12:41 PM
|
Erland is mostly right. However, the connection remains live in the pool for 4-8 minutes (a timeout that can't be altered in non-clustered systems). SQL Express or any version of SQL Server does not implement the pool--it's the client .NET (or OLE DB/ODBC) data provider that implements this functionality. -- __________________________________________________ ________________________ William R. Vaughn President and Founder Beta V Corporation Author, Mentor, Dad, Grandpa Microsoft MVP (425) 556-9205 (Pacific time) Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) http://betav.com http://betav.com/blog/billva __________________________________________________ __________________________________________ "Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote in message news:Xns9D1CEF46A71DEYazorman (AT) 127 (DOT) 0.0.1... Morris Neuman (Morris (AT) online (DOT) nospam) writes: The basic design is over 10 years old when a database connection was an expensive resource so it was shared amongst al the threads. I'm more a telephony software expert than SQL expert so I wasn't sure that a connection for each thread was a best practice for the situation. In that vein is it just as well to create and destroy a connection for each query? Or should each thread have a private connection for the duration of execution? It's very common to write applications where you connect, run a query and disconnect. While this may seem expensive, it does not have to be, because of something known as connection pooling. When you disconnect, the API lingers to the connection for 60 seconds, and if you request for a new connection with the same connection properties within those 60 seconds, a connection will be reused from the pool if necessary. If each thread keeps on connection during its entire lifetime, it can indeed be a little expensive, and take up some memory both client-side and server-side. Do you know if this issue is just for SQL EXPRESS or I will have the same result with all SQL versions? I don't know exactly what you did, but from what you described, I see nothing that would be specific to Express, so, yes, I think you would see the same no matter which edition of SQL Server you would use. Thanks again for your response and the links you supplied. The links are just part of my signature, and not related to this question. -- Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx |
#12
| |||
| |||
|
|
William Thanks so much. I just posted a reply ro Erland that I switched provider (hence client) and the errors stopped. Does that makes sense in light of what you are saying? -- Thanks Morris "William Vaughn (MVP)" wrote: Erland is mostly right. However, the connection remains live in the pool for 4-8 minutes (a timeout that can't be altered in non-clustered systems). SQL Express or any version of SQL Server does not implement the pool--it's the client .NET (or OLE DB/ODBC) data provider that implements this functionality. -- __________________________________________________ ________________________ William R. Vaughn President and Founder Beta V Corporation Author, Mentor, Dad, Grandpa Microsoft MVP (425) 556-9205 (Pacific time) Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition) http://betav.com http://betav.com/blog/billva __________________________________________________ __________________________________________ "Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote in message news:Xns9D1CEF46A71DEYazorman (AT) 127 (DOT) 0.0.1... Morris Neuman (Morris (AT) online (DOT) nospam) writes: The basic design is over 10 years old when a database connection was an expensive resource so it was shared amongst al the threads. I'm more a telephony software expert than SQL expert so I wasn't sure that a connection for each thread was a best practice for the situation. In that vein is it just as well to create and destroy a connection for each query? Or should each thread have a private connection for the duration of execution? It's very common to write applications where you connect, run a query and disconnect. While this may seem expensive, it does not have to be, because of something known as connection pooling. When you disconnect, the API lingers to the connection for 60 seconds, and if you request for a new connection with the same connection properties within those 60 seconds, a connection will be reused from the pool if necessary. If each thread keeps on connection during its entire lifetime, it can indeed be a little expensive, and take up some memory both client-side and server-side. Do you know if this issue is just for SQL EXPRESS or I will have the same result with all SQL versions? I don't know exactly what you did, but from what you described, I see nothing that would be specific to Express, so, yes, I think you would see the same no matter which edition of SQL Server you would use. Thanks again for your response and the links you supplied. The links are just part of my signature, and not related to this question. -- Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.