cancel
Showing results for 
Search instead for 
Did you mean: 

reorg rebuild failed

Former Member
0 Kudos

I try to rebuild all index on a table say mytab like(my db is ASE 12.5):

reorg rebuild mytab

looks like most of indexes rebuild but at the end of the processing, then got following message:

90 REORG REBUILD of 'mytab' terminated due to error or user interrupt.

91 Number (1105) Severity (17) State (1) Server (MYSERVER) Can't allocate space for object 'mytab' in database 'mydb' because 'myseg' segment is full/has no free extents. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE to increase the size of the segment.

then I check segment space with following sql:

select segment = s.name,

       free_pgs = sum(     

       case u.segmap

         when 4 then lct_admin("logsegment_freepages", db_id('mydb'))  - lct_admin("reserved_for_rollbacks", db_id('mydb'))

         else curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs)

       end),

     

       free_M = sum(     

       case u.segmap

         when 4 then lct_admin("logsegment_freepages", db_id('mydb'))  - lct_admin("reserved_for_rollbacks", db_id('mydb'))

         else curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs)

       end)*2/1024

     

from master..sysdevices d ,master..sysusages u ,mydb..syssegments s

where u.dbid = db_id('mydb')

and u.vstart between d.low and d.high and d.status & 2 = 2

and u.segmap & power(2,s.segment) != 0

group by s.name

and got following result (or sp_helpsegment):

segmentfree_pgsfree_M
1default6898491347
2indexes11976572339
4logsegment1019994619921
6myseg12643232469
8system6898491347

all segment have free space!   data and pk of mytab is on myseg, all other indexes is on segment indexes.

3  devices has been assigned for myset. The free space of devices for myseg as below::

   devicefree_pgsfree_M
7dev14563189
8dev26644481297
9dev35542441082

Not sure why. how to figure it out and resolve it?

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

mostly reorgs are better then index rebuilds but occasionally when you're short of space - dropping the indexs and then rebuilding is the better option.

former_member188958
Active Contributor
0 Kudos

How large is the mytab table?  Run

sp_spaceused mytab, 1

A possible factor is that REORG REBUILD, CREATE INDEX, and minimally-logged BCP use a special allocation method that can't use the first extent (which only has 7 usable pages instead of 8 as the first page contains the allocation page that controls the allocation unit).  So these commands can get 1105 errors even when there is some free space still on the segment.

-bret