Issue
I developed Spring Batch Jobs for processing hundreds of thousands flat files. This program long time worked properly. After, batch tables's row count and jobs's execution time increased.
I tried to truncate these tables. And I saw, I should drop some constraints for disabling. And then, I truncated tables at the last, I added constraints that dropped before. This code as shown below and works simultaneously with main program(File Processing).
ALTER TABLE [dbo].[BATCH2_STEP_EXECUTION] drop CONSTRAINT [JOB2_EXEC_STEP_FK]
ALTER TABLE [dbo].[BATCH2_JOB_EXECUTION] drop CONSTRAINT [JOB2_INST_EXEC_FK]
ALTER TABLE [dbo].[BATCH2_JOB_EXECUTION_PARAMS] drop CONSTRAINT [JOB2_EXEC_PARAMS_FK]
ALTER TABLE [dbo].[BATCH2_JOB_EXECUTION_CONTEXT] drop CONSTRAINT [JOB2_EXEC_CTX_FK]
ALTER TABLE [dbo].[BATCH2_STEP_EXECUTION_CONTEXT] drop CONSTRAINT [STEP2_EXEC_CTX_FK]
truncate table BATCH2_JOB_EXECUTION_PARAMS
truncate table BATCH2_JOB_EXECUTION_CONTEXT
truncate table BATCH2_JOB_INSTANCE
truncate table BATCH2_JOB_EXECUTION
truncate table BATCH2_STEP_EXECUTION
truncate table BATCH2_STEP_EXECUTION_CONTEXT
ALTER TABLE [dbo].[BATCH2_JOB_EXECUTION_PARAMS] WITH CHECK ADD CONSTRAINT [JOB2_EXEC_PARAMS_FK] FOREIGN KEY([JOB_EXECUTION_ID])
REFERENCES [dbo].[BATCH2_JOB_EXECUTION] ([JOB_EXECUTION_ID])
GO
ALTER TABLE [dbo].[BATCH2_JOB_EXECUTION_PARAMS] CHECK CONSTRAINT [JOB2_EXEC_PARAMS_FK]
GO
ALTER TABLE [dbo].[BATCH2_JOB_EXECUTION_CONTEXT] WITH CHECK ADD CONSTRAINT [JOB2_EXEC_CTX_FK] FOREIGN KEY([JOB_EXECUTION_ID])
REFERENCES [dbo].[BATCH2_JOB_EXECUTION] ([JOB_EXECUTION_ID])
GO
ALTER TABLE [dbo].[BATCH2_JOB_EXECUTION_CONTEXT] CHECK CONSTRAINT [JOB2_EXEC_CTX_FK]
GO
ALTER TABLE [dbo].[BATCH2_JOB_EXECUTION] WITH CHECK ADD CONSTRAINT [JOB2_INST_EXEC_FK] FOREIGN KEY([JOB_INSTANCE_ID])
REFERENCES [dbo].[BATCH2_JOB_INSTANCE] ([JOB_INSTANCE_ID])
GO
ALTER TABLE [dbo].[BATCH2_JOB_EXECUTION] CHECK CONSTRAINT [JOB2_INST_EXEC_FK]
GO
ALTER TABLE [dbo].[BATCH2_STEP_EXECUTION] WITH CHECK ADD CONSTRAINT [JOB2_EXEC_STEP_FK] FOREIGN KEY([JOB_EXECUTION_ID])
REFERENCES [dbo].[BATCH2_JOB_EXECUTION] ([JOB_EXECUTION_ID])
GO
ALTER TABLE [dbo].[BATCH2_STEP_EXECUTION] CHECK CONSTRAINT [JOB2_EXEC_STEP_FK]
GO
ALTER TABLE [dbo].[BATCH2_STEP_EXECUTION_CONTEXT] WITH CHECK ADD CONSTRAINT [STEP2_EXEC_CTX_FK] FOREIGN KEY([STEP_EXECUTION_ID])
REFERENCES [dbo].[BATCH2_STEP_EXECUTION] ([STEP_EXECUTION_ID])
GO
ALTER TABLE [dbo].[BATCH2_STEP_EXECUTION_CONTEXT] CHECK CONSTRAINT [STEP2_EXEC_CTX_FK]
GO
Sometimes, I get this error
Msg 3728, Level 16, State 1, Line 4
'JOB2_EXEC_STEP_FK' is not a constraint.
Msg 3727, Level 16, State 0, Line 4
Could not drop constraint. See previous errors.
Msg 2714, Level 16, State 5, Line 27
There is already an object named 'JOB2_EXEC_CTX_FK' in the database.
Msg 1750, Level 16, State 1, Line 27
Could not create constraint or index. See previous errors.
Msg 2714, Level 16, State 5, Line 35
There is already an object named 'JOB2_INST_EXEC_FK' in the database.
Msg 1750, Level 16, State 1, Line 35
Could not create constraint or index. See previous errors.
Msg 547, Level 16, State 0, Line 43
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "JOB2_EXEC_STEP_FK". The conflict occurred in database "db_name", table "dbo.BATCH2_JOB_EXECUTION", column 'JOB_EXECUTION_ID'.
Msg 4917, Level 16, State 0, Line 47
Constraint 'JOB2_EXEC_STEP_FK' does not exist.
Msg 4916, Level 16, State 0, Line 47
Could not enable or disable the constraint. See previous errors.
How can I resolve this situation? Thank you very much.
Solution
After a long research I've discover a easiest way.
Using this library. The source code is public on github where you can see an example reading this test.
Manually: First be sure that your server is not running jobs. Then if you are using sql server you can run the Official drop schema script in your DB here. Or if you are using other db you can find an script for you here. Then you can recreate your schema in two ways:
a. Using official script to do it manually. Take a look here if you are using sql server or search your best option here.
b. Turning on spring.batch.initialize-schema property to always. Then restart your server to automatic spring batch schema recreation.
spring.batch.initialize-schema=always
Answered By - Nico
Answer Checked By - Clifford M. (JavaFixing Volunteer)
