LONGTX in Non-logged DB
Folks, During a several million row INSERT, we got a long transaction rollback. But the database, being a data warehouse, is non-logging! Investigation showed that the particular table being loaded was inadvertently created with default EXTENT SIZE and NEXT SIZE. The logs were thus filled with endless DDL entries-- PTEXTEND (partition extend) and CHALLOC (chunk extent allocation) rows. Each extension was 8 pages (16K). I will alter the SQL to allocate proper space and NEXT for the table in question to fix the problem, but... The question is, why didn't extent size doubling (doubling every 16 allocations) happen, thus preventing me from reaping the fruits of my sloppiness? Thanks for your thoughts. David Grove
A thought occurred to me... Could it be that a new extent is allocated, but it is contiguous and thus included in the existing single extent. So the number of extents is still 1. Next time a new extent is added, as far as IDS is concerned, it would still be the first new extent, because the previously added extent was folded into the original extent. Thus the process would continue-- continually allocating new extents of size 16K, because each previous allocation is included in the original single extent, and each new extent is the "first" of the 16 required before doubling occurs. How does this sound? DG David Grove <david_grove@health.state.ak.us> wrote in message news:37eee3cc@news.gci.net... > Folks, > > During a several million row INSERT, we got a long transaction rollback. > But the database, being a data warehouse, is non-logging! Investigation > showed that the particular table being loaded was inadvertently created with > default EXTENT SIZE and NEXT SIZE. The logs were thus filled with endless > DDL entries-- PTEXTEND (partition extend) and CHALLOC (chunk extent > allocation) rows. Each extension was 8 pages (16K). > > I will alter the SQL to allocate proper space and NEXT for the table in > question to fix the problem, but... > > The question is, why didn't extent size doubling (doubling every 16 > allocations) happen, thus preventing me from reaping the fruits of my > sloppiness? > > Thanks for your thoughts. > > David Grove > >