Query log segment usage for a Sybase database where data and log are in same device

Question

Is there a way to get log usage from a database where data and log segment are in the same device for Sybase?

I have searched and there are examples of queries that show all databases and usages, but many commented that when a database segment = 7 (data and log in same device) it will show same values because they are in same device.

When ocurred a log suspend it is not necesary that the data usage is full, this is why i am asking if there is a way to show the real usage of data and log even if there are in the same device.

This is the query i am using:

select 
ceiling(sum(case when u.segmap != 4 then u.size/1048576.*@@maxpagesize end )), 
ceiling(sum(case when u.segmap != 4 then size - curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end)/1048576.*@@maxpagesize), 
ceiling(sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end)), 
ceiling(sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end) - lct_admin('logsegment_freepages',d.dbid)/1048576.*@@maxpagesize) 
from master..sysdatabases d, master..sysusages u 
where u.dbid = d.dbid  and d.status != 256 and u.dbid = db_id('DBNAME') group by d.dbid

Answer 1

Searching the source code for sp_spaceused, i found the section for mixed data and log.

/* 
** For a mixed log and data database, we cannot
** deduce the log used space from the total space
** as it is mixed with data. So we take the expensive
** way by scanning syslogs.
*/
select @total_pages = sum(u.size)
from master.dbo.sysusages u
where u.segmap & 4 = 4
and u.dbid = db_id()

select @used_pages = lct_admin("num_logpages", db_id())
 
/* Account allocation pages as used pages */
select @used_pages = @used_pages + (@total_pages / 256)

After we get the results, we need to convert the pages to KB or MB, so we have to query the page size:

select @@maxpagesize

In my case is 2048 (2K page), that correspond to the value of 512 in Sybase infocenter, so we just need to divide the result of @used_pages / 512 to get the space in MB.