Detecting SQL Server offline in ASP.NET Application

I was recently given an assignment to create an aspx page that shows when backend SQL database is offline. There are two scenarios when a database is unreachable. First, the database server itself is down, .NET framework in this case throws a SQL exception with property "Number" set to 2,53 or –2. Second case, the server is reachable but it refuses to serve the request because of various reasons for example: server paused, login failed, network error and etc. This information is stored in a system table "sys.messages" in master database on SQL server.

In SQL server 2005/2008 under “master” db context run this query:

select * from sys.messages

gives you a list of messages that sql server supports. I wanted only the ones related to database connectivity issue. Here is a list of messages I hand picked for the assignment:

• 2,53: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.
• -2: Client Timeout
• 4060: Cannot open database "%.*ls" requested by the login. The login failed.
• 17142: SQL Server service has been paused. No new connections will be allowed. To resume the service, use SQL Computer Manager or the Services application in Control Panel.*
• 18452: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.%.*ls
• 18456: Login failed for user
• 18312,18320,18330 : sql server service paused.
• 233: A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

Now I have the numbers I need to put them into use somewhere. The best spot seems to be Application_Error function in global.asax.

protected void Application_Error(object sender, EventArgs e)
{
    var exp = Server.GetLastError().GetBaseException();
    if (exp is System.Data.SqlClient.SqlException)
    {
        var sqlExp = exp as System.Data.SqlClient.SqlException;
        if (new[] { -2, 2, 53, 233, 4060, 17142, 18452, 18456, 18312, 18320, 18330 }.Contains(sqlExp.Number))
        {
            Server.ClearError();
            HttpContext.Current.Response.Redirect("~/Error/Offline.htm", true);
        }
    }
}

A last note here I used an .htm error page instead of aspx. In a lot of asp.net applications, there is database access in either Application_Start or Session_Start. If the error page was an aspx page, Application_Start or Session_Start will get called depending on the state of the application when users are being redirected and thus resulting in an infinitely loop.