Hello,

we are still using SQL 10.x (Yea, know, not supported any longer) with global autoincrement in a sql remote replicated environment.

To set a new database_id when needed, we have defined a event which is fired when less than 5000 freed ID's remain. This is fired approx. every 3 months, and worked just flawless in the last 7 years. But this morning.... it did not.

We had freezes of the database engine, such as it was still listening on the TCP port, but connections could not be established. But they did also not return a error or timeout.

After a log of searching (Database validation, lock clients out, restart engine etc.) we did find out that the system did get freezed when it did fire the "GLOBAL AUTOINCREMENT near end of range" trigger.

We have a table which holds the available free range ID's. The code fo the event is this:

begin
  declare @dbID unsigned bigint;
  declare @cmd varchar(500);
  select min(GlobalDBId) into @dbID from dba.db_glob_ids where current publisher = DatabaseID and Available = 1;
  set @cmd='set option public.global_database_id = '
    +cast(@dbID as varchar);
  execute immediate @cmd;
  message 'Setting new dbID with [' || @cmd || ']' to console;
  set @cmd='update dba.db_glob_ids set available=0,taken_at=current timestamp where globaldbid='
    +cast(@dbID as varchar);
  execute immediate @cmd
end

Any idea why it could have freezed the whole engine this morning? (Monday morning is the peak day in number of transactions on that database) So after reducing the number of concurrent accesses suddenly the global_database_id what set correctly by the event/trigger.

The System is running under Debian Squeezy, 64 bit, with SQLA 10.0.3.3900 - 64Bit