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.