SQL SERVER – FIX : Msg 8115, Level 16, Arithmetic Overflow Error Converting IDENTITY to Data Type INT

One of my friends who is a SharePoint expert told that he is getting overflow error while doing check-in on the document. He was confident that the error is related to SQL and that’s why he contacted me. I used profiler and found the table which had the problem and fixed that as well. This blog is an outcome of that work done. Let us see the error related to Arithmetic Overflow Error.

What is an overflow? Whenever we try to insert a value in a column which is more than the limit of data type, we will get an overflow error. In my case, it was an identified column, which was defined as INT and the below are the variations of error when it comes to IDENTITY columns. It all depends on the data type of the column. The number of values permitted would be dependent on data type.

Msg 8115, Level 16, State 1, Line 25
Arithmetic overflow error converting IDENTITY to data type tinyint.
Arithmetic overflow occurred.
Msg 8115, Level 16, State 1, Line 25
Arithmetic overflow error converting IDENTITY to data type smallint.
Arithmetic overflow occurred.
Msg 8115, Level 16, State 1, Line 25
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.
Msg 8115, Level 16, State 1, Line 25
Arithmetic overflow error converting IDENTITY to data type bigint.
Arithmetic overflow occurred.

Here are the steps to reproduce. Keep in mind that tinyint has limit of 255 so if we try to insert 256 in tinyint column, it would fail.

USE [tempdb]
GO
IF OBJECT_ID('SmallTable') is NOT NULL
DROP TABLE [SmallTable]
GO
CREATE TABLE [dbo].[SmallTable] (
	[i] [tinyint] IDENTITY(1, 1) PRIMARY KEY NOT NULL
	,[j] [nchar](20) NULL
	) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SmallTable] ADD CONSTRAINT [DF_SmallTable_j] DEFAULT(N'SQLAuthority')
FOR [j]
GO
SET NOCOUNT ON
GO
-- Inserting and Deleting the rows.
INSERT INTO SmallTable VALUES (DEFAULT)
DELETE FROM SmallTable 
GO 255
-- Below inset would fail as we have already reached 255 (limit of tinyint)
INSERT INTO SmallTable VALUES (DEFAULT)
/*
Msg 8115, Level 16, State 1, Line 21
Arithmetic overflow error converting IDENTITY to data type tinyint.
Arithmetic overflow occurred.
*/
-- check the rows in table and since we are inserting and deleting, its having 0 rows.
SELECT * FROM SmallTable

Here is the screenshot of SSMS

WORKAROUND/SOLUTION

In our above example, we have zero rows so how to verify the identity value? Refer my earlier blog.

SQL SERVER – Find Current Identity of Table

DBCC CHECKIDENT('dbo.SmallTable')

Here is the output

Checking identity information: current identity value ‘255’, current column value ‘NULL’.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Here is the command which I ran to fix the issue

DBCC CHECKIDENT('dbo.SmallTable',reseed,0)

You can read more about the command over here SQL SERVER – DBCC command to RESEED Table Identity Value – Reset Table Identity

I have selected value as TINYINT as its ready to demonstrate the error. On SharePoint issue which I worked, it was integer and due to amount of project, documents and check-ins, they hit the limit.

Have you seen more variations of above error? Please share via comments.

Reference: Pinal Dave (http://blog.SQLAuthority.com)