我可以重置缓存表中的ChangeId以获取更改通知吗

发布于 2024-12-04 12:29:05 字数 192 浏览 1 评论 0原文

如果我重置表中的changeId 值，会发生什么情况

AspNet_SqlCacheTablesForChangeNotification

其中一行当前已达到 2 亿行的最大值，并且我们的更新失败。我尝试将类型更改为 BigInt，但读取它的应用程序失败。我需要将它们重置为 0。可以吗？会不会有问题？

原文

What happens if I reset the changeId value in the table

AspNet_SqlCacheTablesForChangeNotification

One of the rows is currently maxed out at 2.x billion, and our updates are failing. I tried changing the type to BigInt but the application reading it is failing. I need to reset these to 0. Is that ok? Will there be problems?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一影成城 2024-12-11 12:29:05

快速回答是……是的。我们最近遇到了这个问题并提出了同样的问题。我发现许多网站都有与此相关的未解答的问题。经过多次狩猎，我别无选择，只能去寻找。这就是我所做的，而且效果很好。

添加到一个或多个表的触发器正在调用一项更新。应如下所示：

EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'YourTableName'

此存储过程不使用标识列，而是使用常规 INT 列，并通过“changeId = changeId + 1”进行更新。一旦达到最大值，它就会爆炸。按如下方式更改此 UPDATE 语句：

替换：

SET changeId = changeId + 1

替换为：

SET changeId =
    CASE
        WHEN changeId = 2147483647  --Max INT
        THEN 1
        ELSE changeId + 1
    END

它应该如下所示：

ALTER PROCEDURE [dbo].[AspNet_SqlCacheUpdateChangeIdStoredProcedure] 
         @tableName NVARCHAR(450) 
     AS

     BEGIN 
         UPDATE dbo.AspNet_SqlCacheTablesForChangeNotification WITH (ROWLOCK) 
         SET changeId = 
            CASE 
                WHEN changeId = 2147483647 -- Max INT
                THEN 1
                ELSE changeId + 1 
            END
         WHERE tableName = @tableName
     END

The quick answer is... yes. We recently ran into this issue and had the same question. A number of sites I've found had unanswered questions regarding this. After much hunting, I had no choice but to just go for it. Here is what I did, and it worked just fine.

There is an update being called by the trigger added to one or more of your tables. Should look like this:

EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'YourTableName'

This stored procedure is not using an identity column, but a regular INT column and updated by 'changeId = changeId + 1'. Once the MAX value is reached, it, well, blows up. Change this UPDATE statement as follows:

Replace:

SET changeId = changeId + 1

With:

SET changeId =
    CASE
        WHEN changeId = 2147483647  --Max INT
        THEN 1
        ELSE changeId + 1
    END

It should look like this:

ALTER PROCEDURE [dbo].[AspNet_SqlCacheUpdateChangeIdStoredProcedure] 
         @tableName NVARCHAR(450) 
     AS

     BEGIN 
         UPDATE dbo.AspNet_SqlCacheTablesForChangeNotification WITH (ROWLOCK) 
         SET changeId = 
            CASE 
                WHEN changeId = 2147483647 -- Max INT
                THEN 1
                ELSE changeId + 1 
            END
         WHERE tableName = @tableName
     END

回复收藏 0 原文

~没有更多了~