Continuous Arithmetic Overflow exception with SSO in BizTalk Server.

Recently my system event viewer was filled with the below ENTSSO error and all my Biztalk application stopped working.

The error says

Description:
An error occurred while attempting to access the SSO database.
Function: LookupXp
File: lookupserver.cpp:283
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred..
SQL Error code: 0x00001FB3 Error code: 0xC0002A21, An error occurred while attempting to access the SSO database..

The reason for the above error is that the identity column in the SSOX_AuditXPLookup had crossed the maximum limit and this cause the SSO lookup function to failing.
The resolution for this issue is we need to Reseed the identity column of this table, but its always safe to take back up of BizTalk database and Master secret. Also, stop all the process using the database before doing the reseed.

Steps for Reseed:-

Stop ENTSSO Service, BizTalk host instances, IIS and SQL server agent jobs.
Run the cmd in sql server DBCC CHECKIDENT (SSOX_AuditXPLookup, RESEED, 1)
Start the Services that were stopped.

Manual steps to modify the data type of xl_identity from int to bigint are provided at https://support.microsoft.com/kb/980960.

Happy Biztalking!!

Written by
Shashidharan Krishnan \ Kshitj Dattani

Reviewed by
Jainath V R 

Microsoft India GTSC