SQL Monitor GUI Error How to increase Pool Size and timeout?

DonFerguson

I was having performance issues with the SQL Monitor Base monitor, so I had the resources on the base monitor server increased. Now the base monitor is frequently timing out.

The significant part of the error message I am receiving is:

"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."

I have many database instances that this base monitor is connecting to, so beyond the obvious answer of scaling out with another base monitor and repository, are there any configuration options to increase these values.


Here is full message:

/OverviewSideNavigation: 500 Internal Server Error
#5jv.#mkv: NHibernate.ADOException was thrown by method GroupedQuery on service DataPresenterService:
NHibernate.ADOException: While preparing SELECT machines0_.[GroupId] as GroupId1_1_, machines0_.[ClusterId] as ClusterId2_1_, cluster1_.[Id] as Id1_0_0_, cluster1_.[CreatedDate] as CreatedD2_0_0_, cluster1_.[ModifiedDate] as Modified3_0_0_, cluster1_.[IsValid] as IsValid4_0_0_, cluster1_.[IsSuspended] as IsSuspen5_0_0_, cluster1_.[CredentialsDiscriminator] as Credenti6_0_0_, cluster1_.[User] as User7_0_0_, cluster1_.[Domain] as Domain8_0_0_, cluster1_.[Password] as Password9_0_0_, cluster1_.[Name] as Name10_0_0_, cluster1_.[IsCluster] as IsCluster11_0_0_, cluster1_.[IsAddressDetected] as IsAddre12_0_0_, cluster1_.[NodeCount] as NodeCount13_0_0_, cluster1_.[RequestedLicenceLevel] as Request14_0_0_, cluster1_.[EffectiveLicenceLevel] as Effecti15_0_0_, cluster1_.[MW_IsEnabled] as MW16_0_0_, cluster1_.[MW_Start] as MW17_0_0_, cluster1_.[MW_Duration] as MW18_0_0_, cluster1_.[MW_Monday] as MW19_0_0_, cluster1_.[MW_Tuesday] as MW20_0_0_, cluster1_.[MW_Wednesday] as MW21_0_0_, cluster1_.[MW_Thursday] as MW22_0_0_, cluster1_.[MW_Friday] as MW23_0_0_, cluster1_.[MW_Saturday] as MW24_0_0_, cluster1_.[MW_Sunday] as MW25_0_0_ FROM settings.[GroupMachines] machines0_ left outer join settings.[Clusters] cluster1_ on machines0_.[ClusterId]=cluster1_.[Id] WHERE machines0_.[GroupId]=@p0 an error occurred ---> System.InvalidOperationException: 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.
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 NHibernate.Connection.DriverConnectionProvider.GetConnection()
at NHibernate.AdoNet.ConnectionManager.GetConnection()
at NHibernate.AdoNet.AbstractBatcher.Prepare(IDbCommand cmd)
--- End of inner exception stack trace ---
at NHibernate.AdoNet.AbstractBatcher.Prepare(IDbCommand cmd)
at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.LoadCollection(ISessionImplementor session, Object id, IType type)
at NHibernate.Persister.Collection.AbstractCollectionPersister.Initialize(Object key, ISessionImplementor session)
at NHibernate.Event.Default.DefaultInitializeCollectionEventListener.OnInitializeCollection(InitializeCollectionEvent event)
at NHibernate.Impl.SessionImpl.InitializeCollection(IPersistentCollection collection, Boolean writing)
at NHibernate.Collection.AbstractPersistentCollection.Initialize(Boolean writing)
at NHibernate.Collection.Generic.PersistentGenericSet`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
at System.Collections.Generic.List`1.InsertRange(Int32 index, IEnumerable`1 collection)
at System.Collections.Generic.List`1.AddRange(IEnumerable`1 collection)
at #hiv.#KXK..ctor(IEnumerable`1 )
at #Zmv.#Vnv.GetGroups()
at #Zmv.#jg6c.#9Jw(#CJv , #VJw )
at #Zmv.#jg6c.#9Jw(#CJv )
at #Zmv.#Vnv.#9Jw(#CJv )
at #Zmv.#6Jw.GroupedQuery(#Kru , #Aru , #hjv , #hjv )
at #Zmv.#6Jw.GroupedQuery(#Kru , #Aru , #hjv )
at #Mlv.#bmv.GroupedQuery(ChannelTreeMessage`1 )
at #Zjv.#jkv.#cxv(String , MethodInfo , Object[] )
at RedGate.Response.Common.Networking.Client.RpcSynchronousTcpChannel.#cxv(String , MethodInfo , Object[] )
at RedGate.Response.Common.Networking.Client.RpcProxyGenerator.#lkv.Intercept(IInvocation )
at Castle.DynamicProxy.AbstractInvocation.Proceed()
at Castle.Proxies.IDataPresenterServiceProxy.GroupedQuery(ChannelTreeMessage`1 rootQueryTreeMessage)
at #Mlv.#1lv.GroupedQuery(Unit , IStatusLogger , #hjv )
at RedGate.Response.UI.Website.Controllers.OverviewSideNavigationController.Index(Nullable`1 date, String clusterName, String machineName, String sqlServerName, String groupName)
at lambda_method(ExecutionScope , ControllerBase , Object[] )
at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<InvokeActionMethodWithFilters>b__a()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<>c__DisplayClassf.<InvokeActionMethodWithFilters>b__c()
at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
at System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName)
at System.Web.Mvc.Controller.ExecuteCore()
at System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext)
at System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext)
at System.Web.Mvc.MvcHandler.<>c__DisplayClass8.<BeginProcessRequest>b__4()
at System.Web.Mvc.Async.AsyncResultWrapper.<>c__DisplayClass1.<MakeVoidDelegate>b__0()
at System.Web.Mvc.Async.AsyncResultWrapper.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _)
at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`1.End()
at System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult)
at System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

chriskelly

The connection pool is a finite number of connections that the SQL Server instance is allowed to handle. In some cases these connections can all be used up, either because they are all activate, or because they have not been correctly released and returned to the pool.

The error that you indicate shows that the error is affecting the SQL Monitor Base Monitor service querying the Data Repository database.

Something to try is to increase the number of connections in the connection pool. The exact method for doing this differs depending on the version of SQL Server though. This stackoverflow post talks about SQL Server 2008

http://stackoverflow.com/questions/1704 ... erver-2008

I hope this helps.

DonFerguson

Thanks for the response Chris. Unfortunately the link you sent is not helping with the problem. It seems to imply that I need to bump up the user connection settings at the database server; however, it is already configured to 0 (0 = unlimited). All research I have performed on this problem, indicates that the default limit for ADO.Net is 100 connections, and when I run a netstat from the base monitor and count the numbers of connections into the repository DB is exactly at 100. The research indicates that this can only be increased in the application.

If I am missing something else to check at the server (other than user connections setting) please specifically let me know.

Back to my original questions, is there a way I can bump up the value of max pooled connections via a configuration change in the Base Monitor?

DonFerguson

For the benefit of anyone reading this thread, I was able to resolve this issue in a support ticket with Red Gate.

Here was the resolution:

"Chris Kelly (Support)
Dec 17 16:39 (GMT)
Thank you for your reply.
You could try the following.
• Stop the SQL Monitor Base Monitor service
• locate the file: RedGate.Response.Engine.Alerting.Base.Service.exe.settings.config
found here on the SQL Monitor Base Monitor machine: C:\ProgramData\Red Gate\SQL Monitor 3
• Make a copy of the file just in case you decide to reverse the changes. Any incorrect changes to this file can prevent the service from starting.
• locate the <connectionStrings> tag, should be around line 110.
• in the line starting "<add name="DataConnectionString"" find the entry "Packet Size=4096;" and add the setting "Max Pool Size=1000;"
• Save the file and Restart the SQL Monitor Base Monitor service.
The change can easily be reversed by deleting the edited file and renaming the copy. You can also adjust the "Max Pool Size" as you see fit.
Chris Kelly
Technical Support Engineer
"[/i]