reorg rebuild failed

[Former Member]

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):

	segment	free_pgs	free_M
1	default	689849	1347
2	indexes	1197657	2339
4	logsegment	10199946	19921
6	myseg	1264323	2469
8	system	689849	1347

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::

	device	free_pgs	free_M
7	dev1	45631	89
8	dev2	664448	1297
9	dev3	554244	1082

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

[Former Member]

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]

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