March 25, 2010 at 3:08 am
Hi,
Can anyone assist with this issue please?
When editing stored procedures via query analyser, once 100 query connections are established the session crashes out with the following error message, (Please note maximum number of concurrent connections is set to zero (unlimited). Can this be rectified, or is it a feature of SQL 2008?
===================================
Attempt to retrieve data for object failed for Server '[My Server Name]'. (Microsoft.SqlServer.Smo)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attempt+to+retrieve+data+for+object+Server&LinkId=20476
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.Server.GetSmoObject(Urn urn)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.ScriptExistingTextObjectToWindow(IManagedConnection mc)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.InvokeTextObject(IManagedConnection connection)
at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.Invoke()
===================================
Failed to connect to server triton64. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at Microsoft.SqlServer.Management.Common.ConnectionManager.PoolConnect()
at Microsoft.SqlServer.Management.Common.ConnectionManager.get_ServerVersion()
at Microsoft.SqlServer.Management.Common.ServerConnection.get_TrueName()
at Microsoft.SqlServer.Management.Smo.Server.CheckValidUrnServerLevel(XPathExpressionBlock xb)
at Microsoft.SqlServer.Management.Smo.Server.GetSmoObjectRec(Urn urn)
at Microsoft.SqlServer.Management.Smo.Server.GetSmoObjectRec(Urn urn)
at Microsoft.SqlServer.Management.Smo.Server.GetSmoObjectRec(Urn urn)
at Microsoft.SqlServer.Management.Smo.Server.GetSmoObject(Urn urn)
===================================
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. (System.Data)
------------------------------
Program Location:
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
March 25, 2010 at 4:12 am
:blink: Query Analyzer? Not SQL Server Management Studio? :blink:
Also, are you saying that you are opening 100 separate connections to the server to edit 100 different stored procedures? Why?
100 connections is default limit for connection pooling as the error message suggests.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 25, 2010 at 4:17 am
Sorry, I'm a bit old school, should be management studio, I refer to QA on the grounds that it looks, walks and quacks like a duck!
Why 100 connections, a developer is reviewing his stored procs and is opening lots of connections.
Limit to 100 max?, Does this apply just to SQL 2008, as I have successfully opened 100 Plus in SQL 2000, also concurrent connections property is set to unlimited (or am I being dim?).
March 25, 2010 at 4:31 am
Fishbarnriots (3/25/2010)
Why 100 connections, a developer is reviewing his stored procs and is opening lots of connections.
100 connections is the default limit for a connection pool - that's a general thing and not particular to SSMS.
I notice there is a Connect item open for this: http://connect.microsoft.com/SQLServer/feedback/details/366094/ssms-2008-max-pool-size-was-reached
So there may not be a workaround. I'll dig a bit more and see what I can find.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 25, 2010 at 7:14 am
I guess it's a silly question, but why would you want to open 100 procedures all at the same time? You can't possibly look at or manipulate them all at once. Whether or not there should be an enforced limit, it seems like there should be a logical limit.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 25, 2010 at 7:22 am
Ok, so there's not too much to be done here since SSMS does not expose the connection string or pooling options directly.
I haven't found a satisfactory workaround, but you do get a new connection pool every time the connection string changes, so I guess you could register the server again under an alias, and connect to Object Explorer via that. Anything that changes the connection string will do. Horrible really, though.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 25, 2010 at 7:31 am
I've done some more digging, it seems that even if opening a stored proc using "Modify", then closing, if repeated 100+ times, same result. If this is indeed a logical limit, than a gentler notification of the issue would be nice.
When I can I am going to try various scenarios, use the option disconnect after query executes etc. Although this would be a bigger pain as having to reconenct after each execution would be time consuming and inconvenient.
March 25, 2010 at 7:39 am
Fishbarnriots (3/25/2010)
...it seems that even if opening a stored proc using "Modify", then closing, if repeated 100+ times, same result.
It would surprise me if that were the case, since the limit is 100 active connections per pool.
Not about to go and open and close 100 stored procedures just to find out though 😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 9, 2010 at 7:53 am
I agree, I never had a need to open 100 connections like that and probably never will.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply