broadlog id primary sequence exhaustion causing duplicate key error

[Shrutii]

Hi all,

broadlog id primary sequence exhaustion causing duplicate key error in our Adobe V7 instance

I am looking for solution to fix this issue

Please help if anyone has faced and got this fixed

in our case, we have on prem instance so adobe support is not able to help much

they are saying u need to put your sequence in negative sequence

but how and where we need to do this, they have no idea

please please help me provide solutions

Re

[Amine_Abedour]

Hello @Shrutii,

The simplest solution is to reduce (if possible) the retention time of broadlogs. By default the retention time is 180 days. You can reduce it to just 90 days if there is no functional need to keep 6 months of logs.

Otherwise if you would like to go in the negative sequence route, check if the sequence used by your broadlog table has no minimum value (assuming your backend database is a postgreSQL one) :

Br,

Amine

[Shruti1]

hi amine

please help to provide info how  can we put our sequence in negative route??

i am am unable to find any doc

eaee help to tel me steps in detail

even we raised adibe ticket but no one from them know this

[Somyash · Accepted solution]

Hi ,

The simplest solution is to reduce the retention time of broadlogs.

By default the retention time is 180 days                                                                                               

When the sequence reaches more than 2 billion (2,147,483,647 is the exact number), it goes back to zero,  the best solution is to switch to negative IDs, starting from -2,147,483,647.

I have applied and its working for me since 5years

In a very simple way , without extending your schema and changing anything else, you can simply run an ALTER Table command to have your sequence which generates IDs to restart with -2147483647

The command would have to run on the DB and the syntax would be something like :

ALTER sequence <Sequence name>
MINVALUE -2147483647
MAXVALUE 2147483647
RESTART WITH -2147483647 CACHE -2147483647 CYCLE;

ALTER SEQUENCE <Sequence name> INCREMENT BY 1;

This will reset the Sequence to restart with -2147483647 and will start assigning IDs incrementally after -2147483647                                                                                                  Try this and let me k now if it works

[Somyash]

As best practice , you can use below code in JS activity in workflow to get the

min and max of your sequence first

var cnx=application.getConnection()
var seqname="xtknewid"; //var seqname="nmsbroadlogid
//Get the sequence info
var request = "SELECT * FROM " + seqname + ";";
var response = cnx.query(request);
var result=";
for each (var row in response)

logInfo("sequence: " + row[0] + "|last value: "
+ row[1]+ "|start value:" + row[2]+"|Increment by:" + row[3]+"|max value:"+row[4]+ "|min value:" row[5]+ "| cache value:" +
row[6])