Red Gate forums :: View topic - SQL Monitor GUI Error How to increase Pool Size and timeout?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 3
SQL Monitor 3 forum

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

Search in SQL Monitor 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
DonMan



Joined: 25 Aug 2008
Posts: 62

PostPosted: Fri Dec 13, 2013 6:30 pm    Post subject: SQL Monitor GUI Error How to increase Pool Size and timeout? Reply with quote

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)


Last edited by DonMan on Mon Dec 16, 2013 8:48 pm; edited 1 time in total
Back to top
View user's profile Send private message
chriskelly



Joined: 19 Apr 2010
Posts: 328
Location: Cambridge, UK

PostPosted: Mon Dec 16, 2013 10:53 am    Post subject: Reply with quote

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/17047358/connection-max-pool-size-in-sql-server-2008

I hope this helps.
_________________
Chris Kelly
Technical Support Engineer
Back to top
View user's profile Send private message
DonMan



Joined: 25 Aug 2008
Posts: 62

PostPosted: Mon Dec 16, 2013 5:51 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
DonMan



Joined: 25 Aug 2008
Posts: 62

PostPosted: Fri Dec 20, 2013 7:29 pm    Post subject: Reply with quote

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]
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group