Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

newbie logsegment question 1

Status
Not open for further replies.

ksauerwald

Programmer
May 11, 2007
1
US
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..
 
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".
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top