Arithmetic Overflow error converting to data type int while reorganizing index in SQL Server

Viewed 199

We have a table of 3+ billion rows and one of the non clustered indexes that we are trying to reorganize contains only one numeric(18,0) column. It is a foreign key column and recently went past the threshold for reorganization. I was wondering if anybody could shed some light on the cause of this issue.

Following is the statement (names modified) that runs as a part of index maintenance.

ALTER INDEX [IX_IndexName] ON  [DB].[dbo].[TableName] REORGANIZE

CREATE TABLE STATEMENT

CREATE TABLE [dbo].[ProBillEditLogTbl](
[ProBillEditLogTbl_Key] [numeric](18, 0) IDENTITY(1,1) NOT FOR REPLICATION 
  NOT NULL,
[ProBill_Key] [numeric](18, 0) NOT NULL,
[EditUserName] [varchar](50) NOT NULL,
[EditDate] [datetime] NOT NULL,
[EditDescription] [varchar](2000) NOT NULL,
[EditApplication] [varchar](25) NULL,
[msrepl_tran_version] [uniqueidentifier] NOT NULL,
[ProBillEditLogType_key] [int] NULL,
[OldProbillNo] [varchar](50) NULL,
[OldPbStatusCode_Key] [int] NULL,
[OldStatusReason] [int] NULL,
[EditedFrom] [varchar](100) NULL,
[ArchiveKeepIND] [bit] NULL,
CONSTRAINT [PK_ProBillEditLogTbl] PRIMARY KEY CLUSTERED 
(
   [ProBillEditLogTbl_Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

 ALTER TABLE [dbo].[ProBillEditLogTbl] ADD  CONSTRAINT 
 [DF_ProBillEditLogTbl_EditDate]  DEFAULT (getdate()) FOR [EditDate]
 GO

ALTER TABLE [dbo].[ProBillEditLogTbl] ADD  DEFAULT (newid()) FOR 
[msrepl_tran_version]
GO

ALTER TABLE [dbo].[ProBillEditLogTbl] ADD  DEFAULT (1) FOR 
[ProBillEditLogType_key]
GO

ALTER TABLE [dbo].[ProBillEditLogTbl]  WITH CHECK ADD  CONSTRAINT 
[FK_ProBillEditLogTbl_ProbillEditLogTypeTbl] FOREIGN 
KEY([ProBillEditLogType_key])
REFERENCES [dbo].[ProbillEditLogTypeTbl] ([ProBillEditLogType_key])
GO

ALTER TABLE [dbo].[ProBillEditLogTbl] CHECK CONSTRAINT 
[FK_ProBillEditLogTbl_ProbillEditLogTypeTbl]
GO

ALTER TABLE [dbo].[ProBillEditLogTbl]  WITH CHECK ADD  CONSTRAINT 
[FK_ProBillEditLogTBL_ProBillTBL] FOREIGN KEY([ProBill_Key])
REFERENCES [dbo].[ProBillTBL] ([ProBill_Key])
ON UPDATE CASCADE
ON DELETE CASCADE

CREATE INDEX Statement

CREATE NONCLUSTERED INDEX [IX_ProBillEditLogTbl_Probill_key_only] ON [dbo]. 
[ProBillEditLogTbl]
(
    [ProBill_Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = 
ON, FILLFACTOR = 95) ON [Indexes01]
GO

ERROR SHOW :

Arithmetic overflow error converting expression to data type int. [SQLSTATE 22003] (Error 8115). The step failed.
0 Answers
Related