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
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.