0

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
Anell Zheng
  • 25
  • 5
  • I'd recommend reviewing the source code for `sp_spaceused` (and `sp_spaceusage` if working on ASE 16) to see how space is reported for `syslogs` – markp-fuso Dec 03 '20 at 14:03
  • 1
    @markp-fuso I have looked the section of sp_spaceused for the mixed data and log usage part to get log usage. It gets the result in page usage. I will post the answer to mark it as the solution. – Anell Zheng Dec 03 '20 at 17:18

1 Answers1

0

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.

Anell Zheng
  • 25
  • 5
  • You should use select @@maxpagesize not @@pagesize - the latter will return 2048 on any page size Sybase dataserver giving you incorrect info. . – Rich Campbell Dec 10 '20 at 06:58