how increase segment on live?

[Former Member]

got error on ase 12.5:

segment is full/has no free extents. If you ran out of space in syslogs, dump transaction log, otherwise, use Alter database to increase the size of the segment

should not the syslog issue, because is dumped every hour.

How to increase segment on live without stop ase?

[kevin_sherlock · Accepted solution]

also, dump tran only releases comitted transactions.  There can very well be an open transaction that is not commited (or rolled back) yet that keeps the logsegment growing despite "frequent" dump trans that are issued.  secondary truncations points in replication systems that aren't moving due to replication issues can also cause this.

select * from master..syslogshold

will show you open trans keeping the log from truncating.

[Former Member · Accepted solution]

sorry, typing mistake. log is dump every hour. log is on separate segment.

Want to extent the segment. want to make sure it is safe to run it on live production.

select * from master..syslogshold  -- no row from this query

sp_helpdb out

	name	db_size	owner	dbid	created	status
1	mydb	32500.0 MB	sa	5	Nov 19, 2010	no options set
	name	attribute_class	attribute	int_value	char_value	comments
1	mydb	buffer manager	cache binding	1	myseg base cache	[NULL]
	device_fragments	size	usage	created	free kbytes
1	mysegsbase	1000.0 MB	data only	Nov 19 2010 11:49AM	16
2	mysegsjobs	1000.0 MB	data only	Nov 19 2010 11:49AM	0
3	mysegspay	4000.0 MB	data only	Nov 19 2010 11:49AM	2468722
4	mysegsindex	3000.0 MB	data only	Nov 19 2010 11:50AM	0
5	mysegsorders	3000.0 MB	data only	Nov 19 2010 11:50AM	1194628
6	mysegsjobs2	1000.0 MB	data only	Nov 19 2010 11:50AM	0
7	mysegspay2	4000.0 MB	data only	Nov 19 2010 11:50AM	1834230
8	mysegsorders2	3000.0 MB	data only	Nov 19 2010 11:51AM	551522
9	mysegslogs	5000.0 MB	log only	Nov 19 2010 11:51AM	not applicable
10	mysegsorders3	2000.0 MB	data only	Nov 29 2010 10:26AM	29710
11	mysegsjobs	500.0 MB	data only	Nov 29 2010 11:16AM	0
12	mysegsjobs2	500.0 MB	data only	Nov 29 2010 11:16AM	0
13	mysegspay3	2000.0 MB	data only	Jan  4 2011 10:51AM	0
14	mysegsjobs	500.0 MB	data only	Apr  1 2011  4:22PM	0
15	mysegspay3	2000.0 MB	data only	Mar  1 2012  3:14AM	561044
1	log only free kbytes = 5099130

[kevin_sherlock · Accepted solution]

Ok.  Separate log segment that is dumped every hour.  Between dumps, you had a full log.  So, you either need to increase your log segment size (although ~5Gb is rather large), or increase the frequency of dumps, or setup a threshold to detect a growing log segment and initiate a dump tran.  Setting up threshold procedures are well documented if you want to look into that.  My guess is that you had a user updating/inserting/deleting lots of rows of a large table
or many tables, and holding that transaction open for a long time.  It's hard to say from a third party perspective what is going on in your system, but these are your options.

alter database <dbname> log on <newlogdevice> = <size> is "safe" and "online".

[Former Member · Accepted solution]

I try following way:

EXEC sp_extendsegment 'mysegsjobs','mydb','mydevice'

mydevice is a device which only has a few small objects on it and have a lot space room.

not sure if this will resolve the problem.

[kevin_sherlock · Accepted solution]

No.  To me that looks like you are extending the wrong segment, and mixing purposes of your devices as well.

You said your logsegment filled.  If I were you, i'd simply create another "log only" device (much like your current 'mysegslogs' device), and extend your database log onto it.

disk init
name = 'mysegslogs2', physname = <osdevicename>, size = <size>, ...

go

alter database mydb log on mysegslogs2 = <size>

go

OR, I would increase the frequency of your log dumps (5G is a LOT of log space)

OR, I would find the offending process that is filling 5G or so of log, or holding a transaction open for a long time, etc,

OR, I would create a threshold procedure to dump tran, or help find offending log space users, etc

[Former Member · Accepted solution]

thank you Kevin. then want to know how to monitor the segments or devices if the space is nearly over in real time?

[former_member188958 · Accepted solution]

Thresholds are often used for that purpose, sometimes triggering an extended stored procedure to email (page, otherwise notify) the DBA.

You can use sp_helpsegment interactively to find the free space on a segment.

1> sp_helpsegment "default"

2> go

segment name    status

------- ------- ------

       1 default      1

device size   free_pages

------ ------ ----------

master 17.0MB       1667

[... lots more output...]

You can look at the source code for sp_helpsegment to find out where it is getting it's numbers

if you want a more direct query:

use sybsystemprocs

go

sp_helptext sp_helpsegment

go

-bret

[kevin_sherlock · Accepted solution]

As I said, one method to monitor segment utilization is by using thresholds which are well documented in the manuals.

sp_helpdb also illustrates how one can use builtin functions and  the system tables such as sysusages, sysdevices, etc to determine how much space is being consumed and how much is free.

Here is a very untested piece of sql to list, both pages free by device fragment and segment, and then grouped and aggregated by segment - derived from the code in sp_helpdb:

select db_name(u.dbid) as "dbname",d.name,s.name,
case u.segmap
when 4 then str(lct_admin("logsegment_freepages", db_id('mydb'))
                                - lct_admin("reserved_for_rollbacks", db_id('mydb')),12)
       else str(curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs),12)
end as "free_pgs"
from master..sysdevices d
inner join
     master..sysusages u
on d.vdevno = u.vdevno
inner join
     syssegments s
on u.segmap & power(2,s.segment) != 0
where u.dbid = db_id('mydb')
and   d.status & 2 = 2
order by u.dbid,u.lstart

select db_name(u.dbid) as "dbname",s.name,
case u.segmap
when 4 then str(lct_admin("logsegment_freepages", db_id('mydb'))
                                - lct_admin("reserved_for_rollbacks", db_id('mydb')),12)
       else str(sum(curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs)),12)
end as "free_pgs"
from master..sysdevices d
inner join
     master..sysusages u
on d.vdevno = u.vdevno
inner join
     syssegments s
on u.segmap & power(2,s.segment) != 0
where u.dbid = db_id('mydb')
and   d.status & 2 = 2
group by db_name(u.dbid),s.name,u.segmap

go

dbname                         name                           name                           free_pgs
------------------------------ ------------------------------ ------------------------------ ------------
mydb                           user_dev_01                    system                                 2682
mydb                           user_dev_01                    default                                2682
mydb                           user_log_01                    logsegment                           209093
mydb                           user_dev_01                    system                                 4480
mydb                           user_dev_01                    default                                4480
mydb                           user_log_01                    logsegment                           209093
mydb                           user_dev_02                    system                               159548
mydb                           user_dev_02                    default                              159548
mydb                           user_dev_02                    system                               255000
mydb                           user_dev_02                    default                              255000
mydb                           user_dev_01                    system                               249112
mydb                           user_dev_01                    default                              249112
mydb                           user_dev_01                    system                               255000
mydb                           user_dev_01                    default                              255000

(14 rows affected)
dbname                         name                           free_pgs
------------------------------ ------------------------------ ------------
mydb                           default                              925822
mydb                           system                               925822
mydb                           logsegment                           209093

(3 rows affected)

[Former Member · Accepted solution]

thank you. On ASE 12.5, there is no column vdevno in master..sysdevices and master..sysusages.

[kevin_sherlock · Accepted solution]

ah yes.  Then the join criteria between sysusages u, and sysdevices d is

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

[Former Member · Accepted solution]

Thanks, Kevin. You mean use

from master..sysdevices d inner join master..sysusages u on (u.vstart between d.low and d.high and d.status & 2 = 2)

to replace

from master..sysdevices d inner join master..sysusages u on d.vdevno = u.vdevno

When I try that, there is no row returned.

[kevin_sherlock · Accepted solution]

post your entire SQL statement that doesn't return rows.

[Former Member · Accepted solution]

select db_name(u.dbid) as "dbname",d.name,s.name,

case u.segmap

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

                                - lct_admin("reserved_for_rollbacks", db_id('mydb')),12)

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

end as "free_pgs"

--from master..sysdevices d inner join master..sysusages u on d.vdevno = u.vdevno

from master..sysdevices d inner join master..sysusages u on (u.vstart between d.low and d.high and d.status & 2 = 2)

inner join syssegments s on u.segmap & power(2,s.segment) != 0

where u.dbid = db_id('mydb')

and   d.status & 2 = 2

order by u.dbid,u.lstart

[kevin_sherlock · Accepted solution]

Ok, does this return anything?  I assume you actually have a database named 'mydb'.

select db_name(u.dbid) as "dbname",d.name,s.name,

case u.segmap

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

                                - lct_admin("reserved_for_rollbacks", db_id('mydb')),12)

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

end as "free_pgs"

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

order by u.dbid,u.lstart

[Former Member · Accepted solution]

it's working! compare with sp_helpsegment, same result, but more convenience. Thanks, Kevin.

[kevin_sherlock]

you didn't post the entire error message as it specifies _which_ segment is full.  It's also important to know if your database devices have mixed segments, or if you have separated logsegment onto separate device(s).  Post the output of "sp_helpdb <dbname>".

If it's the logsegment, then follow the instructions given.  IE, dump tran (if your logsegment is on dedicated devices), or alter database to extend the database.

You mention "because is dumped every hour", but what is dumped?  If it is transaction log, that would imply you have separate logsegment which is good, but just because it is dumped every hour doesn't mean it still can't fill up between dumps.  That depends on the transaction load being serviced, not the frequency of the dumps.