topic Re: Error RSPortal: obtaining a connection from the pool in Report Server

Error RSPortal: obtaining a connection from the pool

Anonymous — Mon, 01 Feb 2021 16:16:06 GMT

Hi,

we use Oct. 2020 release (product version 1.9.7627.11028) and have the following Internal Server Error:

2021-02-01 10:54:28.3246|ERROR|190|OData exception occurred: 
System.Exception: ReportServerDatabaseUnavailableException --->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.

I can reproduce this error when i open one of our Power BI Reports ( Live Connection to Tabular) and navigate through the tabs and filter values. After each click i can see increasing connections on SQL Server DB ReportSever DB via sys.sysprocesses.
When it reaches 100 connections , the error occurs and the Report Service is unavailable( empty root folder) for some minutes.

100 fits to the following Log entry in RSPortal after restarting PBIRS:

2021-02-01 12:05:10.4767|INFO|1|Catalog max connection pool size: 100

What is RsPortal used for? Older Power BI report do not show this behaviour.

Is it a bug and how can i resolve or work around the problem?

Is it fixed in Januar 2021 release?

Re: Error RSPortal: obtaining a connection from the pool

Petebro — Mon, 01 Feb 2021 17:16:20 GMT

RSPortal is the process that hosts the UI for browsing your reports. AKA http://{ServerName}/Reports runs in RSPortal process and talks to the catalog to fetch various information. I am unaware of any issue where we suddenly require extra connections to reach some sort of upper limit of connections available. It is not something we specifically changed in October or fixed in January.

Have there been any changes to the SQL instance hosting the catalog? Is it possible to run the profilers on the catalog to see if there are a huge number of connections coming in from somewhere?

Re: Error RSPortal: obtaining a connection from the pool

Anonymous — Tue, 02 Feb 2021 09:57:21 GMT

Hello,

No changes to the SQL instance. There isn't a large number of connections by RSPportal in sys.sysprocesses, just up to 100. What do you mean by "catalog"? DB or SQL Server instance?

Re: Error RSPortal: obtaining a connection from the pool

Anonymous — Tue, 02 Feb 2021 10:59:24 GMT

Hi again,

I was able to narrow down the problem:

Some of our older Live Connection Power BI reports don't even open one connection via RSPortal to SQL Server, while others keep increasing the number of connections with each new click (filter, drill down).

I opened one of the old Power BI reports (build with a 2019 desktop version) in my up to date Desktop Version (Oct. 2020) and saved it again to PBIRS.

Now, with each click in this new Version the number of connections increase and i am able to reproduce the error mentioned above.

Looks like a bug in the Desktop Version.

Please advise.

Re: Error RSPortal: obtaining a connection from the pool

Petebro — Tue, 02 Feb 2021 16:21:12 GMT

Catalog refers to the ReportServer database that stores state for the Power BI Report Server instance. Usually named "ReportServer" but you can view this from the RSConfig.exe tool when you connect to your PBIRS instance.

Re: Error RSPortal: obtaining a connection from the pool

Petebro — Tue, 02 Feb 2021 16:25:17 GMT

Ah let me engage with someone from the desktop team that is more familar with that area I will be back with investigation outcomes.

Though would it be possible to setup an enviroment using the January release of PBI RS And PBI RS Desktop to ensure this wasn't already fixed since October?

Re: Error RSPortal: obtaining a connection from the pool

jbarta_mlchc — Tue, 02 Feb 2021 18:17:48 GMT

I have been getting this error as well ever since the October 2020 update. I was hoping the January 2021 update would fix it, but it does not. In the October 2020 release, the issue appeared to cause the Home screen to be blank once the error occurred and I would have to reboot PBIRS to get it to appear again. The blank Home screen appears to not happen with the January 2021 release. This is the full error I am seeing in the RSPortal log file:

2021-02-02 10:04:50.1524|ERROR|235|OData exception occurred: System.Exception: ReportServerDatabaseUnavailableException ---> 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.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.BIServer.HostingEnvironment.Storage.MeteredSqlConnection..ctor(String connectionString)
--- End of inner exception stack trace ---
at Microsoft.BIServer.HostingEnvironment.Storage.MeteredSqlConnection..ctor(String connectionString)
at Microsoft.BIServer.Configuration.Catalog.CatalogAccessFactory.NewConnection()
at Microsoft.ReportingServices.CatalogAccess.Streams.VarbinaryReadableStream..ctor(String getContentStoredProcedure, Dictionary`2 parameters)
at Microsoft.ReportingServices.CatalogAccess.Streams.VarbinaryReadableStreamFactory.CreateExtendedContentReadableStream(Guid catalogItemId, ExtendedContentType contentType)
at Microsoft.ReportingServices.Portal.Repositories.CatalogItemRepository.GetCatalogItemWithContentInternal(IPrincipal userPrincipal, RSService rsService, String path, Boolean internalUse)
at Microsoft.ReportingServices.Portal.Repositories.CatalogItemRepository.GetCatalogItemWithContentTrusted(IPrincipal userPrincipal, Guid key)
at Microsoft.ReportingServices.Portal.ODataWebApi.Common.CatalogItemControllerHelper`1.GetItem(String key)
at Microsoft.ReportingServices.Portal.ODataWebApi.V2.Controllers.CatalogItemsController.GetEntity(String key, String castName)
at Microsoft.ReportingServices.Portal.ODataWebApi.Controllers.Reflection.EntitySetReflectionODataController`1.GetRoot(ODataPath oDataPath, Int32& index)
at Microsoft.ReportingServices.Portal.ODataWebApi.Controllers.Reflection.ReflectionODataController`1.Get(ODataPath oDataPath)
at lambda_method(Closure , Object , Object[] )
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass6_2.<GetExecutor>b__2(Object instance, Object[] methodParameters)
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__5.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ExceptionFilterResult.<ExecuteAsync>d__6.MoveNext().| RequestID = 4f5e7c00-4014-2159-000a-2aacfe265f20 ClientSessionID = fc2378e1-3ab0-f57e-4a80-34c19e60aa6d

Re: Error RSPortal: obtaining a connection from the pool

Petebro — Tue, 02 Feb 2021 18:23:04 GMT

Thanks for the detail. Are you seeing the number of connections grow with Power BI Reports using direct query or are you seeing it with other types of models as well?

Re: Error RSPortal: obtaining a connection from the pool

jbarta_mlchc — Tue, 02 Feb 2021 18:35:11 GMT

Sorry, don't post frequently so I think I responded to the wrong message. My apologies. The majority of my reports are direct query to SSAS tabular model. I do have a few that are direct query to a SQL Server database, but I am hesitant to update any of those or edit them due to this issue. I can make a copy and see if the issue occurs. But I am only seeing this issue with direct query with a new report I created with the October 2020 release. I opened it in January 2021 and saved it again in hopes it fixed it, but as I stated it did not fix the issue. I have actually put a pause on all editing and new reports since the October 2020 release because of this issue. I will test a direct query to a SQL Database and let you know.

Re: Error RSPortal: obtaining a connection from the pool

jbarta_mlchc — Tue, 02 Feb 2021 18:41:35 GMT

Forgot to mention that to test the data source (SSAS Tabular model) for performance issues, I recreated the report/dashboard as a paginated report and the report did not experience the same issue. So i do not believe it is a problem with my tabular model.

Re: Error RSPortal: obtaining a connection from the pool

jbarta_mlchc — Tue, 02 Feb 2021 19:13:23 GMT

I did a test on a report with direct query to a SQL Server database and I did not have the issue. The report does not have as many slicers and visuals as the tabular model direct query report that has issues, so it might not be a good comparison. But those reports that I have SQL Server direct query are very limited, so it is all I had.

I did do another test to see if I can get the blank Home screen to show and unfortunately it did this time. I have to reboot PBIRS from the RS Configuration Manager for the Home screen to show our folders again. Additionally, it causes all other reports that connect to the same SSAS instance to throw a something went wrong error.

Re: Error RSPortal: obtaining a connection from the pool

jbarta_mlchc — Tue, 02 Feb 2021 21:29:14 GMT

I have done some further testing and discovered a few other things. Our clinic has not moved on to the new Edge browser yet. I installed it and ran the report from the web portal like I have been and there were no issues. I did not experience the connection pool error and the home screen did not go blank. I am only experiencing that when running Internet Explorer 11. I also tested adding the ?rs:Embed=True to the report URL in the new Edge and that caused the issue with the connection pool and the home screen went blank. I use that to deploy reports from a website. But the report runs with no issues in the new Edge and with out the Embed on the URL.

Re: Error RSPortal: obtaining a connection from the pool

jbarta_mlchc — Tue, 02 Feb 2021 21:31:23 GMT

What browser are you using? I just did some further testing and it does not seem to happen with the new Edge browser. I was wondering if you are using IE 11.

Re: Error RSPortal: obtaining a connection from the pool

Anonymous — Wed, 03 Feb 2021 08:10:13 GMT

I see increasing connections with every browser I use: Firefox (78), Edge (87), Chrome (88).
I don't think this has anything to do with the browser, but with the desktop version that provides the Power-Bi report on the server.

Re: Error RSPortal: obtaining a connection from the pool

Steffe — Wed, 03 Feb 2021 16:24:05 GMT

Hi,

I think we have the same problem.

I see the same error-message in our "rsportal"-log. We are using oct 2020 version (15.0.1104.239) of the server.

I have tried to reproduce it and for some reports (in my example it was a Live Tabular report) i can increase the number of SQL connections by for example changing a filter/slicer in the report. But in my test it seems like I can increase the number of conection to a certain level (can be below 100) by changing a filter, but then it just stop increasing it. I´ve not been able to "kill" the report-portal while doing my own tests.

I´ve never been able to get a higher number of connections than 100.

I´m also looking in the sys.processes view to count the number of connections to 'RSPORTAL'.

I´m not sure which version of the desktop the user that created this report where using. Can I see it in some way (perhaps in the reportserver database)?

I´ve got calls from users saying the report portal is sometimes not responding and I think it is because of this problem.

Re: Error RSPortal: obtaining a connection from the pool

jbarta_mlchc — Wed, 03 Feb 2021 17:06:53 GMT

I am trying to understand further how you are seeing the connections. I see a view called sys.sysprocesses and I run that against the PBIRS database. What should I be looking for to see the connection and seeing them increase? Thanks for you help in understanding this further.

Re: Error RSPortal: obtaining a connection from the pool

jbarta_mlchc — Wed, 03 Feb 2021 17:40:00 GMT

Nevermind I figured it out and now I can see the connections increase. I did some testing with the new Edge browser versus IE 11 and one thing I noticed is that there were far less connections per filter or slicer change in the new Edge versus IE 11. I was seeing it increase by ~4 connection in the new Edge and around ~24 connections in IE 11 with each filter/slicer change. It took a lot more changes to reach the 100 before the portal became unresponsive.

Re: Error RSPortal: obtaining a connection from the pool

jbarta_mlchc — Wed, 03 Feb 2021 17:44:26 GMT

Thanks for your reply. After understanding where to watch the connections I am now seeing the issue with the new Edge browser as well. I noticed that there were less connection per filter/slicer change with the new Edge browser when compared to IE 11. I was seeing ~24 connections in IE 11 and ~4 with the new Edge. So it took longer for the portal to become unresponsive using the new Edge giving me the false impression that it was resolved using the new Edge browser.

Re: Error RSPortal: obtaining a connection from the pool

jbarta_mlchc — Wed, 03 Feb 2021 18:05:12 GMT

I had another question. Shouldn't these connections drop once the user closes the report? I am not seeing the connections decrease after I close a report.

Re: Error RSPortal: obtaining a connection from the pool

Anonymous — Thu, 04 Feb 2021 08:02:30 GMT

This is my understanding of rsportal and a connection pool:

rsportal is configured to use a maximum of 100 pooled connections. When it has reached 100, no new connection may be established. One of the 100 pooled connections must be used. If all connections are still being used (possibly incorrectly), the portal will no longer respond. When the timeout is reached, server logs the error msg.

After a few minutes you can see that the number of connections are falling and Report Server is responding again.

Petebro Did you get any feedback from the Desktop RS guys?