Really need some suggestion on SQLCacheDependency.

I have some problem with ASP.NET cache, I found other people has similar problem, but I didn't find real solution.

The one bother me most is the SQLCacheDependency doesn't work stable. I insert object in cache and has SQLCacheDependency linked. After a period of time, it stopped working. That means the the object is still in cache, but change on db side doesn't remove the cache entry. I am not sure if it is ASP side or SQL side, I feel it is ASP side.

I am using 2.0 + SQL 2005.

Once the db command notification stop working, you have to restart IIS or clear all items in cache since you don't kno which one is changed.

Any suggestion?

Noone help? no MVP in this forum? or I should post this on .NET 2.0 forum?

Does the problem exist? or it is not a problem, just my code? in either case, I think I need some confirmation.

I have been used the cache for almost one year, I still have the same issue today. Will someone from SQL team or ASP.NET team see this post?

|||This is pretty late but have you solved your problem yet? Did you find a solution?|||

No, I can not find a way to test what is wrong, it never works consistent for me. Now I can not rely on notification from db. Every time when I change my record, I have to tell my web server to clear the relative cached items.

|||Post some code where you are inserting the item in the Cache.

Thanks,|||

string cacheKey = LinkSites.GetMappedKey(virtualPath, fileid.ToString()); // this will return a key from virtualPath
if (!String.IsNullOrEmpty(cacheKey)) frd = (FileRecordData)HttpContext.Current.Cache[cacheKey];
if (frd == null)
{
int siteid = 0;
SqlCacheDependency scd = null;
lock (_connection)
{
try
{
SqlCommand sqlcmd = new SqlCommand("select ownerid,id,uniqueid,parentid,category,name,content,dated=isnull(updated,created),created,updated,isdirectory from dbo.link_sourcestore whereid=@.id", Connection);
sqlcmd.CommandType = CommandType.Text;
SqlParameter sqlparam;
sqlparam = sqlcmd.Parameters.Add("@.id", SqlDbType.Int);
sqlparam.Value = fileid;

scd = new SqlCacheDependency(sqlcmd);
using (SqlDataReader reader = sqlcmd.ExecuteReader())
{
if (!reader.HasRows) return null;
reader.Read();
siteid = LinkRoutine.Convert(reader["ownerid"], 0);
frd = GetRecordData(reader);
}
}
catch (Exception e)
{
ErrorHandler.Report("GetCachedFileRecord 2 [" + realVirtualPath + "," + virtualPath + "]", e);
return null;
}
}

if (scd != null)

{
frd.CacheKey = cacheKey;
frd.CacheDependency = scd;
HttpRuntime.Cache.Insert(cacheKey, frd, scd, Cache.NoAbsoluteExpiration, new TimeSpan(24, 0, 0), CacheItemPriority.NotRemovable, new CacheItemRemovedCallback(LinkCacheHandler.RemovedCallback));
}
}

It just read the record and add into cache, when cache item removed, call the static method RemovedCallback in LinkCacheHandler, LinkCacheHandler is posted below. After I restart IIS, it will work for a while, 5, 10 or more minutes, but after a while, even I set breakpoint in RemovedCallback, I don't get anything when I change the record. (when I call my clear cache method, which will remove all records from cache, it runs to the breakpoint. So the callback is fine)

public class LinkCacheHandler
{
public static void RemovedCallback(string k, object v, CacheItemRemovedReason r)
{
if (!k.Contains("system/cache.ascx"))
{
LinkSites._cacheLog += "RemovedCallback[" + DateTime.Now.ToString() + "]<br/> " + k + ((v is FileRecordData)?(" : " + ((FileRecordData)v).CacheKey) : "") + " " + r.ToString() + "\n<br/>";
LinkSites.NotifyCacheObject(k);
}
}
}

|||There are various conditions on which it wont work. Like I think you need to use the full names in your SQL SELECT queries like dbo.Categories instead of Categories. Also you cannot use the aggregate functions like SUM, COUNT and things like that. Make sure your queries are free from all those. You can check book online to find more restrictions.

I also see that you are using HttpRuntime.Cache.Insert. Try using HttpContext.Current.Cache.Insert instead.

Hope it helps!|||

Well, you can tell from my code I pasted I didn't break any of the rules. And the situation is not it never works, it just doesn't work stable.

I read an article talks about HttpRuntime.Cache and HttpContext.Current.Cache, they don't have difference, except the HttpRuntime.Cache is a little efficient.

There are many articles about how to use cache, but they are the same, just list out some general commands. I guess what I find out is how to trace the cause of the problem when the cache stop working. You know there are several steps to push the change from db to web server. So far, I havn't found any article goes deep on that.

|||

Quick note: I believe HttpContext.Current.Cache implicitly calls HttpRuntime.Cache so they're functionally identical.

Beyond that, the one thing I can mention that I discovered at first is that a callback isn't called every time the database changes, it's called every time the cache is invalidated. This obviously means that if you haven't repopulated the cache, you won't hit your breakpoint when you change your data.

This makes perfect sense: the callback is for cache item invalidation and there's no item to invalidate if the cache isn't repopulated. Nevertheless it caught me off-guard when I started working with the SqlCacheDependency (though I don't know why it should have).

I don't know if you are having a similar brain fart, but otherwise I can also suggest you might try checking out the SQL Server MSDN forums here:http://forums.microsoft.com/MSDN/default.aspx?ForumGroupID=19&SiteID=1.

I've had some pretty good luck getting questions answered there.

|||

Hmm, it doesn't look like it can happen in my code. Everytime cache item dependency changes, the cache item will be removed from cache, and it will call the callback. And when I use a object, I always try to get it from cache. If it is not in cache, I will read it and insert it in cache before use it.

When it stopped working, I found the items were still in cache, but it doesn't be removed when data changed.

|||

Well, what I mentioned is more about operator-error/misperception than faulty code, but if the item is still in the cache, you're right: you're not experiencing the problem. I know it sounds silly, but I spent about 15 minutes wondering why my callback function wasn't getting hit when data changed, and it was simply because the data was already invalidated/ not in cache.

What you say makes me a little nervous, however, since I'm using the SqlCacheDependency in a similar way. If you nail down the problem please post back here and let us know what was up so we know how to avoid potential stability issues!

|||

I hope I can, I havn't solved the problem for a year. Since I started to use the beta last year.

Another thing may not may relate to this, you can see I set my cache to NoAbsoluteExpiration and expire date is 24 hours. But if no activities on the site, all cached items will be cleared after 20-30 minutes. I had set the applicaiton pool to make sure it doesn't recycle worker porcess. This happens on both my dev and live servers.

|||

There is a know issue with .Net 2.0 and SQLCacheDependency that causes the caching to stop working after 5 minutes. Here is the link to the hot fix.

http://support.microsoft.com/kb/913364/EN-US/

Tyler