Custom Search
www.rocket99.com : Technical Guides Sybase Oracle UNIX Javascript


Technical Guides
Sybase
Oracle
UNIX
Javascript




Of Interest

Business Intelligence and Analytics



Oracle Training





Sybase » Administration » Monitoring » Utilities

Transaction Log Checking: Wait Until It Clears

     



This procedure will check the state of the transaction log,
and will pause until is is cleared or until @pct_limit mb are free.
This is helpful for batch systems which hammer a lot if data into the
database at a quick pace.

Be sure to comment out the 12.X code if you are using ASE 15.X!






create proc sp_sybase_logcheck (@mode varchar(50) = null, @pct_limit money = 80 ) as



declare
@pct money,
@mb1 money,
@log_size money

set nocount on

-- ASE 12.X
select @mb1 = ( data_pgs (8, doampg) ) * (@@maxpagesize/1000.0) / 1000
from sysindexes where id = 8

-- ASE 15.X
select @mb1 = ( ( data_pages (db_id(), 8, doampg) ) * (@@maxpagesize/1000.0) / 1000.0)
from sysindexes where id = 8

select @log_size = sum(size)
from master.dbo.sysusages u
where u.dbid = db_id() and u.segmap = 4

select @pct = @mb1 / @log_size * 100



while @pct > @pct_limit
begin

if @mode like '%verbose%'
select 'Trans log % full is .... ', @pct

waitfor delay "00:00:10"


-- ASE 12.X
select @mb1 = ( data_pgs (8, doampg) ) * (@@maxpagesize/1000.0) / 1000
from sysindexes where id = 8

-- ASE 15.X
select @mb1 = ( ( data_pages (db_id(), 8, doampg) ) * (@@maxpagesize/1000.0) / 1000.0)
from sysindexes where id = 8

select @log_size = sum(size)
from master.dbo.sysusages u
where u.dbid = db_id() and u.segmap = 4

select @pct = @mb1 / @log_size * 100
end



return
go


-- Also of interest, and alternate method of obtaining transaction log usage:

select lct_admin('logsegment_freepages',db_id() ) * (@@maxpagesize / 1000.0) / 1000.0 as LOG_FREE_MB
go









Sybase : Related Topics

Sybase : Administration : Display Locks on Tables

Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site



Get the latest Rocket99 news and tech tips via






Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site




Copyright © 2016 Stoltenbar Inc All Rights Reserved.