Monthly Archives: March 2014

Intermittent Error: Could not find stored procedure ‘dbo.aspnet_CheckSchemaVersion’

So this error had been causing me grief for about 9 months.  The problem was on a site that only my business partner and I use,d so we just dealt with it.  But we were getting ready to launch our latest project (textbooklook.com) and we starting getting the error there too.  That was unacceptable. I eventually found the answer here

How to Reproduce Error

Log in to site and go to a page that requires authorization. Let the site stand idle for 30 minutes (don’t let anybody else use this site either).  Click onto another page that requires authorization.

Result: Error that says “Could not find stored procedure ‘dbo.aspnet_CheckSchemaVersion’” blah, blah blah.

 

Relevant Technologies and Conditions

  • .NET Simple Membership Class (May also apply to the standard .NET Membership Class)
  • Using the Role Provider
  • Using [Authorize(Roles = "Admin")] or Roles.IsUserInRole or User.IsInRole
  • MVC .NET (may or may not be relevant)
  • Using SQL Server for the Membership Class/Role Provider tables  (may or may not be relevant)
  • Low traffic website (very relevant)

 

The Problem

Because the site was idle (with no other traffic), the application pool on the IIS server “fell asleep”, well actually it was killed.

I’m no expert in this area (and if someone can explain it better, please let me know), but I think it goes something like this.  IIS server is holding open a connection pool in the application pool.  When the site needs to access the database, it uses this connection that is still in memory.  But after a while of inactivity, IIS says, “you know, no one is using this site, so I might as well release the resources back to the OS.” And the application pool dies killing the connection pool with it. Then when someone hits the site again, IIS says “whoops, better get this going again” and while it’s warming up, there is a database call to see if the user is authorized to view the requested page, somewhere along this line I’m guessing the call is trying to use the old connection pool that doesn’t exist yet (or anymore) and so it answers back, “hey, I don’t see no stinkin’ database”…barf.

 

Solution

Keep that application pool alive. Never let it die.

How? I ended up signing up for UptimeRobot which checks to see if my website is functioning properly by sending out an http request every 5 minutes.  This answer came from Joe Audette on mojoPortal (there is another option and another service you can read about there).

I love the UptimeRobot solution because it kills three birds with one stone.  It keeps my site alive so I don’t get that horrible error, it warns me if the site goes down, also it keeps my site snappy fast because it never has to “warm up” after a period inactivity.