We have a SSIS custom log table where we insert log entries for each step & every step performed by the job. We have plenty of jobs in place running in different frequencies. Each job fills the log entries to the same table. This results the log_ID column (datatype: INT) reaches it maximum limit every month. This causes the subsequent jobs would fail.

For now we are manually truncating the log table once we start getting errors on the jobs.

We are looking for some Auto Clearing process where the SSIS custom log table should be cleared automatically as soon as it reaches the maximum limit.

We have already analyzed two solutions below but these were not seems to be optimal with less code changes.

1. Creating a DML trigger to remove records (but this may impact the performance of our jobs since each step is inserting records to log tables so for each insert the trigger would fire)
2. Extending the column data type from INT to BigINT (but this requires lot of code change)

Kindly suggest us some optimal solution with less code changes.