newbie logsegment question 1

[ksauerwald]

I'm trying to write a sp that returns the max size and the current size of the logsegment in sybase.. to be proactive and monitor it... anyone know any sql I can run to do this.. I'm no DBA I just write normal procs to insert/update and the like so I dont know much about the system tables..

any insights would be great..

 

[salmontraining]

The standard way to monitor the transaction log is to put a "free-space threshold" on it. Basically, you tell Adaptive Server to do something when the log gets X percent full. You can get ASE to call a stored procedure that YOU write - this proc can dump the transaction log, or write to an error file etc.

It's not too hard to add a threshold - the syntax is:
exec sp_addthreshold <db>, logsegment, <nr of pages>, <stored proc>

The part that takes a bit of knowledge is working out how many pages (space) to set as the threshold. First you calculate the total size of the log:

select sum(size) from master..sysusages
where dbid = db_id(<db>) and (segmap & 4) = 4

Then if you want the threshold to fire at say 60% full, multiple the result from the above query and set the number of pages to sp_addthreshold to that result.

Hope that makes some sense. If you want more info, the write-up is in the Sys Admin product manual in chapter "Managing Free Space with Thresholds".