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
|
|
|
Get the latest Rocket99 news and tech tips via
|
