We have a table named [Session] which is replicated from DBS-MASTER to DBS-SUB1 and DBS-SUB2 using transactional updatable replication. The structure of the table (simplified) is as follows:
CREATE TABLE [Session] ( SessionId INT IDENTITY(1, 1) NOT NULL, UserId UNIQUEIDENTIFIER NOT NULL, SessionToken UNIQUEIDENTIFIER NOT NULL )
As this is an article in a transactional updatable subscription, SQL Server has been left to look after partitioning the identity values into ranges on each of the servers. Up until recently this has worked entirely as expected and without issue, however we've seen two issues, one of which caused us a major issue (service impacting due to session creation failure!):
- The check constraint on DBS-MASTER was stale, meaning that the range had been exhausted and no new records could be inserted into the table. Running
sp_adjustpublisheridentityrange(As documented in this stackoverflow question) resolved the issue once we determined what the problem was and how to solve it.
- We've seen the table have no CHECK constraint on it at all, for a period of time. It looks like this was self-corrected when the highest used identity value on DBS-MASTER ticked up to hit the threshold set in
Item 2 is less of a concern than item 1, but still looks odd and is being detailed as it may help to identify what the problem is here.
- the table consumes around 60,000-100,000 identity values a day
- the table is almost exclusively inserted into on DBS-MASTER
- there are other columns in the table (which I can describe if required) but the exact structure of the table doesn't feel related to the problem
- there are business logic level constraints on the number of rows present per UserId, which are managed by deleting older entries before adding new ones
- the table is ordinarily never more than 10-15,000 rows in size
- What can we do to mitigate against the issue we saw with the range specified by the constraint being exhausted and the constraint not having been updated?
- What could be causing constraint exhaustion and failure to be updated?