Caroline Sosebee is a Software Engineer at ThreeWill. She has 30+ years of software development experience and a broad scope of general IT knowledge. At ThreeWill she has a proven track record of quality consultative and software development skills, focusing on the design and implementation of SharePoint solutions, both on-premises and in the Microsoft 365 cloud.
Did You Get SqlClient.SqlException (0x80131904)?
We recently ran across a very interesting and NOT (or barely) documented problem on one of our client’s on-premises SharePoint 2013 farms which was a doozy to solve.
The site with the problem is in a very large SharePoint farm being used as a central repository for reports that are accessed by employees throughout the company. Many of the reports are published automatically via Business Objects while many others are created and uploaded manually. The net of it is that there are thousands and thousands of files being added/manipulated daily (or weekly or monthly) within the document libraries in this site. All this activity creates a lot of churn in the databases and ultimately caused SharePoint to hit what would seem to be an impossible to reach value.
The problem first manifested itself as an inability to upload a new document or to edit the properties of an existing document. When searching the ULS logs, we found that it was triggering the below SQL error:
Error: SqlClient.SqlException (0x80131904): Arithmetic overflow error converting IDENTITY to data type int.
Definition: “An arithmetic overflow is caused by a calculated column value that exceeds the column’s specified size. When this error occurs, the calculation stops and the remainder of the results pane is not filled.”
Finding the Culprit
So the next step was to figure out which SQL table was throwing this arithmetic overflow error. We didn’t have direct access to the SharePoint SQL database, so we had someone on site who did have access to run a set of queries for us. The first one was used to determine which tables had an ‘ID’ column that could be throwing this error:
SELECT t.TABLE_NAME ,c.COLUMN_NAME ,c.TABLE_CATALOG ,c.TABLE_SCHEMA FROM INFORMATION_SCHEMA.COLUMNS AS c JOIN INFORMATION_SCHEMA.TABLES AS t ON t.TABLE_NAME = c.TABLE_NAME WHERE COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME) ,c.COLUMN_NAME,'IsIdentity') = 1 AND t.TABLE_TYPE = 'Base Table' AND t.TABLE_NAME NOT LIKE 'dt%' AND t.TABLE_NAME NOT LIKE 'MS%' AND t.TABLE_NAME NOT LIKE 'syncobj_%'
We then ran a query on each of the tables in order to check their current values:
select max(SQId) as SiteQuota from SiteQuota: 2116070153 select max(Id) as Versions from Versions: 9 select max(Id) as AllFileFragments from AllFileFragments: Null select max(Id) as SolutionResourceUsageDaily from SolutionResourceUsageDaily: Null select max(Id) as EventCache from EventCache: 637542714 select max(Id) as SolutionResourceUsageWindowed from SolutionResourceUsageWindowed: 494160 select max(Id) as SolutionResourceUsageLog from SolutionResourceUsageLog: Null select max(Id) as SiteDeletion from SiteDeletion: Null select max(Id) as SiteVersions from SiteVersions: 134
As you can see from the results, it was pretty obvious which table was probably causing the error. What wasn’t so obvious is what this table did and what we could do about it.
IDENTITY columns are of type ‘int’ which allows for a maximum value of 2,147,483,647 (2 billion +) – a really huge number. Even though the current max value of the identity column on the SiteQuota table wasn’t this exact number, it definitely showed us that this one was the likely culprit. We confirmed by running the below query, which is used to return the last identity value generated for the table:
Returned value was 2,147,483,647
Do No Harm
Unfortunately, we had no idea what this table did so weren’t sure if we could reseed it or not. We only knew that the ‘SiteQuota’ table was a SharePoint system table that Microsoft does not publicly document (that we could find).
By running a series of tests on a local system though, we could tell that it was used every time a SharePoint item was touched (added / removed / updated) and that the IDENTITY column was being incremented by 2 each time one of these actions happened. We could also see that this was apparently a temporary ‘working’ table, meaning that items are added to it for the duration of the transaction then deleted.
Given this, we were able to successfully reseed the IDENTITY column so that the system could get back online. We ran the following commands to do this and confirm success:
dbcc checkident ('sitequota', reseed, 0)
– this reset the identity column back to 0
– this verified that it was reset correctly
After doing this, all was now right in this particular world!
For a typical SharePoint site, we think this limit should never even come close to being reached, which is probably why we had such a hard time finding any information about it out there. But this is such a huge SharePoint farm that this particular client will need to continue to monitor this value on an ongoing basis in order to keep the system from grinding to a halt again.
I hope this helped you – if it did, please leave a comment below and, as always, feel free to ask me questions.