Share and Enjoy !

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_%'

Output:

TABLE_NAMECOLUMN_NAMETABLE_CATALOGTABLE_SCHEMA
SiteQuotaSQIdWSS_Content_RRPdbo
VersionsIdWSS_Content_RRPdbo
AllFileFragmentsIdWSS_Content_RRPdbo
SolutionResourceUsageDailyIdWSS_Content_RRPdbo
EventCacheIdWSS_Content_RRPdbo
SolutionResourceUsageWindowedIdWSS_Content_RRPdbo
SolutionResourceUsageLogIdWSS_Content_RRPdbo
SiteDeletionIdWSS_Content_RRPdbo
SiteVersionsIdWSS_Content_RRPdbo

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:

Select ident_current('SiteQuota') 

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.

The Solution

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

select ident_current('sitequota') 

– 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.

Share and Enjoy !

Related Content: