Azure SqlException: Database on server is not currently available

Question

Our site has been running for a few weeks in Azure without getting this error:

SqlException: Database 'database' on server 'server' is not currently available. Please retry the connection later. If the problem persists, contact customer support, and provide them the session tracing ID of 'guid'.

It finally got that one day when there were a little over 2K of active (concurrent) users. This is the closest question that I can find in SO. We are not using EF though but rather we're using Dapper. I'm out of ideas how to debug our application to find out what caused the issue, and it's even harder now that the issue has not come up for the past 2 days. I definitely need to be on the lookout and I need you guys, any tip, on where I should be looking into, what I need to do to determine the cause of the issue, and possibly fix it.

Answer 1

I was experiencing the same error with a serverless Azure SQL Database. It was set with Auto-pause delay in 1 hour of inactivity. Here is some explanation of the issue with db unavailability: https://www.codejourney.net/how-to-fix-sqlexception-database-is-not-currently-available-on-azure-0x80131904/

It’s possible that you experience some delay in the ramp-up of the compute power after idle periods. In extreme circumstances, it’s even possible that the underlying host cannot provision enough resources within a few minutes. In that case "load balancing" will automatically occur. During load balancing the database remains online except for a brief period at the end of the process when all connections are dropped. This behavior is comparable with resizing a database in any provisioned tier.

Lastly, after an auto-pause the first connection to the database will fail.

For me the connection restores automatically after 10-15 minutes after the first load of the app without implementing any retry logic in my code. (I use this db just for testing purposes though.)

So in conclusion, the solution is: if this is happening because the db is set as serverless, to change the tier.

Answer 2

We went down the path of handling transient errors with the Azure Transient Fault Block, but this caused bigger issues - namely, if you reach the SQL connection limit (easy to do), having retry logic in place only makes things worse.

If it only happens once a month, I'd leave it be, and just handle it gracefully higher up the stack. An alternative is to create a custom retry policy to avoid retrying on certain errors, but it may still do more harm than good.

Answer 3

It sounds like you need to handle transient failures via some sort of transient fault handling mechanism. Here is post asking a similar question: SQL Azure Database retry logic David's answer is similar to the approach we took do deal with the issue.

Here is another link to some code that is similar to the David's and our solution to get your head around it. http://www.getcodesamples.com/src/4A7E4E66/41D6FAD

We had similar issues when we first moved to SQL Azure but by implementing back-off retry logic for the transient connection issues the majority of the time it recovers after a few seconds.