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?

do you know how/where the old valud of changeID is kept ? – Royi Namir Jan 08 '13 at 18:59 — Royi Namir, Jan 08 '13 at 18:59

score 3 · Accepted Answer · answered Feb 28 '12 at 17:29

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

Thanks for the answer! It's funny - I no longer work for the company this was an issue for and the client has since dropped the contract with my former employer, but it's still good to know. — DaveDev, Mar 02 '12 at 11:49
Glad it was useful. I know it was frustrating for me not finding and answer, so hope this assists anyone that runs into it. — KD-kbajan, Apr 23 '12 at 19:07

Can I reset the ChangeId in Cache Table for Change Notification

1 Answers1