Misleading SQL server error

The problem

This error has caught me more than once, and when you realize what the problem is you understand why. The error you receive is this: >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.

Initial thoughts

My initial thought was that we were having network issues, and started looking in that direction, which seemed reasonable. Pings were working, but the problem was getting worse. More errors were being reported.

So what is going on

The problem turns out to be that the database was running out of disk space. I could see it had no room to write, so I looked at the drive the database was on, and it had plenty of room. So What is going on?

The Real Problem

The real problem is my database is setup to expand when it needs space. When it does need space, it is set to increase the size of the database by 20%. <<<<<<< HEAD

This works great when the database is created. It starts life as a 1 meg db and the first time it needs to expand, it takes 200k more space and the fun continues. The problem happens as the database gets larger. When it hits 10 gig, that 20% is now expanding the database by 2 gig!

So what is happening is, the application is trying to insert data into the database. The database then realizes that it needs more space, and then tries to expand. Your app, having a timeout on the connection, waits 30 seconds or whatever your timeout is, and throws our error above. The server is still trying to expand the space for the database, gets the error, and gives up on expanding the drive. The next connection comes in and the problem starts all over.

======= This works great when the database is created. It starts life as a 1 meg db and the first time it needs to expand, it takes 200k more space and the fun continues. The problem happens as the database gets larger. When it hits 10 gig, that 20% is now expanding the database by 2 gig! So what is happening is, the application is trying to insert data into the database. The database then realizes that it needs more space, and then tries to expand. Your app, having a timeout on the connection, waits 30 seconds or whatever your timeout is, and throws our error above. The server is still trying to expand the space for the database, gets the error, and gives up on expanding the drive. The next connection comes in and the problem starts all over. >>>>>>> 2019-07-21_Get_Lets_Encrypt_working_with_Azure_Functions Now when this happens to your database will depend on the speed of your drives and speed of your SQL computer.

Solution

To get your app running expand the database from the SQL Management Studio. It won’t timeout, and it will solve the immediate problem.

Next you need to make some decisions. You can expand the drive to a large fixed amount, or you can change the way your database expands from a percentage to a fixed amount that can be done by your server in a reasonable time consistently.

Other ways this can happen

When you are importing large amounts of data into a database this can happen quick. Again you database can quickly increase its size when it is small but as it gets larger it will have the same problem.

Conclusion

This happens often enough to write this quick post.