1

In this project I'm using MVC-3, with output cache attributes and the appropriate references in the web.config and, hopefully, the global.asax (below). I'm running production from a shared hosting and can't run aspnet_regsql as a admin to create the appropriate objects, so I copied the AspNet_SqlCacheTablesForChangeNotification table, the SPs and triggers up from my dev box to setup the database.

In general, everything works great, however I'm finding that about once every 2 or 3 days all of the caching database objects are deleted and not getting recreated on app start. (no hosting co script is deleting them).

To get the site working again, I end up manually copying the database objects back up.

How can I not have the objects deleted, or, alternatively, have them automatically created?

My app start in the global.asax looks like this:

protected void Application_Start()
{
     RegisterGlobalFilters(GlobalFilters.Filters);

     SqlCacheDependencyAdmin.EnableNotifications(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
    SqlCacheDependencyAdmin.EnableTableForNotifications(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString, "be_Posts");
    SqlCacheDependencyAdmin.EnableTableForNotifications(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString, "be_PostTag");

     SiteMap.Provider.SiteMapResolve += new SiteMapResolveEventHandler(SiteMapPathExpansionResolver.OnSiteMapResolve);
    RegisterRoutes(RouteTable.Routes);
}

and my app end looks like this:

protected void Application_End()
    {
        SqlCacheDependencyAdmin.DisableTableForNotifications(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString, "be_Posts");
        SqlCacheDependencyAdmin.DisableTableForNotifications(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString, "be_PostTag");
        SqlCacheDependencyAdmin.DisableNotifications(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
    }

web.config entry looks like this:

<caching>
  <sqlCacheDependency enabled="true"   pollTime="10000"   >
    <databases>
      <add name=" db"
         connectionStringName="ApplicationServices"
         pollTime="10000"
      />
    </databases>
  </sqlCacheDependency>
</caching>
Eran Egozi
  • 775
  • 1
  • 7
  • 18
Peter K
  • 25
  • 7

1 Answers1

0

So I effectively solved it by commenting/stripping out the Disables from the Application_End. I had read that this leaves sql server with open handles, but I didn't find any other solution that worked. I tried adding create triggers and startup events to recreate the tables and such and a number of other approaches, but only removing the Disables seemed to work.

I hope the shared and db server I'm on doesn't have issues, but the hosting company hasn't complained to me and there's really no way for me to know if this approach causes complications because they don't grant access to that kind of monitoring.

Peter K
  • 25
  • 7