Never Leave Connection Open

Today, we hit upon an issue where our App Service did intermittently hang up. We checked the performance level attributes like Memory and CPU which were on ordinary levels and so as Application Insights which did not highlight any major exceptions apart from the routine ones. What really helped us narrow down the problem is the Azure App Service’s Diagnose and Solve Problem. We ran a few quarries on “Web App Down” and “Web App Slow” and it eventually pointed out that Multiple sessions were trying to call a new connection, every time it initiated. Further, we narrowed down data, saw that one particular function which updates real-time revenue constantly was the culprit for the error; It was a SQLDataReader which was not closing up its connection and subsequently, multiple instances got created, leaving with large amounts of connections piling up and eventually falling victim for time-outs. We also noticed that there were 114 active connections which were way too big to have. Furthermore, we also saw more unique sessions and users compared to ordinary days which is also another reason to this error to prop up other than on earlier days.

After resolving the issue, we were able to bring down the active connection to 21 and the Web app to perform well.

I fixed the issue by bringing the following piece of code in our Model

datareader = Sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);

and made sure that datareader.close() was called after the operation is finished.

What we used for this operation

1. Executing Azure SQL Server’s system Stored Proc to getting active connection: exec sp_help and exec sp_help2
2. Azure App Service’s Diagnose and solve Problems function; Select Health Check under Availability and Performance

a.       Under this Select Web App Down

b.       Then Select Slow Request Stack Tracking

c.       Which leads us to the Stack Traces.

Source: https://stackoverflow.com/questions/20469899/c-sharp-invalid-attempt-to-call-read-when-reader-is-closed/20470976?noredirect=1#comment106092515_20470976