dotnet / dotnet/aspnetcore

SqlServerCache not emptying expired items

Open
#28,370 3 comments 0 reactions 0 assignees View on GitHub
area-middleware feature-caching investigate
Dominant language
C#
Stars
38.4k
Forks
10.9k
Avg merge
2d 10h
Merged PRs (30d)
281

Description

### Describe the bug

I am seeing a scenario where the database table created for SqlServer-based DistributedCache is not being emptied properly. The number of items in the table seems to grow without bound.

### To Reproduce

We are using ASP.NET Core 3.1.1:

```xml

```

Unfortunately I don't know how we got into this situation as I observed it just now, and I don't have specific steps to reproduce.

We store items into the cache using `AbsoluteExpirationRelativeToNow` and there is no sliding expiration.

### Expected behavior

I expect the cache to be emptied consistently to avoid growing out of bounds.

### Screenshots

```sql
SELECT COUNT(*) Count, MIN(ExpiresAtTime) Oldest
FROM dbo.DistributedCache
WHERE GETDATE() > ExpiresAtTime;
```

returns

| Count | Oldest |
|-|-|
| 139962 | 2020-03-21 02:12:05.8058460 +00:00 |

### Additional context

I've looked at the code that does the delete: [src/Caching/SqlServer/src/SqlQueries.cs#L56](https://github.com/dotnet/extensions/blob/f4066026ca06984b07e90e61a6390ac38152ba93/src/Caching/SqlServer/src/SqlQueries.cs#L56). This seems a bit naive to me if there are many entries to be deleted, for some reason. If I run the code, our database seems to choke. I do see an index on `ExpiresAt` but it doesn't seem to help.

Contributor guide

Open the contributing guide

Research direction

Start by inspecting src/Caching/SqlServer/src/SqlQueries.cs at line 56 and the expired-item deletion query. Use the reported DistributedCache table query to measure expired rows and investigate behavior with many entries; done means expired cache items are removed consistently without the database choking.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.