Can I reset the ChangeId in Cache Table for Change Notification

Question

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?

Answer 1

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