Use the following SQL statement in order to get information about the size and usage of the data and log segments in all databases:
select db_name(d.dbid) as db_name,
ceiling(sum(case when u.segmap != 4 then u.size/1048576.*@@maxpagesize end )) as data_size,
ceiling(sum(case when u.segmap != 4 then size - curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end)/1048576.*@@maxpagesize) as data_used,
ceiling(100 * (1 - 1.0 * sum(case when u.segmap != 4 then curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end) / sum(case when u.segmap != 4 then u.size end))) as data_used_pct,
ceiling(sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end)) as log_size,
ceiling(sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end) - lct_admin("logsegment_freepages",d.dbid)/1048576.*@@maxpagesize) as log_used,
ceiling(100 * (1 - 1.0 * lct_admin("logsegment_freepages",d.dbid) / sum(case when u.segmap in (4, 7) then u.size end))) as log_used_pct
from master..sysdatabases d, master..sysusages u
where u.dbid = d.dbid and d.status != 256
group by d.dbid
order by db_name(d.dbid)
This will display 7 columns:
- The name of the database
- The total size of the data segments of this database (in MB)
- The usage of the data segments of this database (in MB)
- The usage percentage
- The size of the log segments of this database (in MB). This columns will be empty if there is no separated log segment i.e. this database has mixed log/data segment
- The usage of the log segments of this database (in MB). This columns will be empty if there is no separated log segment i.e. this database has mixed log/data segment
- The usage percentage. This column will contain no value if there is neither a separate log segment nor a mixed log segment (e.g. for the dbcc database). If there is a mixed data/log segment, it will display the same percentage as the data segment usage.
A few details about the query:
@@maxpagesize returns the server’s logical page size. It’s basically the same value you’d get by using the following:
select low from master.dbo.spt_values where number = 1 and type = "E"
So multiplying a number of pages by @@maxpagesize returns the corresponding number of bytes.
1048576 is the number of bytes in a megabyte. Dividing by 1048576 thus converts all number of bytes in number of megabyte. The dot after 1048576 is used so that we implicitly convert those integers to floats. Here an example of why we need this dot:
- 1048575 / 1048576 * 4096 returns 0 as 1048575 / 1048576 is 0 when doing some pure integer arithmetic
- 1048575 / 1048576. * 4096 returns 4095.99606784
When computing the values you have to make sure that you avoid arithmetic overflow which would happen e.g. if you multiplied by @@maxpagesize before dividing by 1048576.
The values of master..sysusages.segmap mean the following:
- 3: Data stored on this segment
- 4: Log stored on this segment
- 7: Since 7=4+3, both log and data stored on this segment
That’s why we exclude segmap = 4 when computing values for the "data" columns, consider only segmap = 4 for the columns related to the size of the log segment or it’s usage. But we do consider both segmap=4 and segment=7 for the usage percentage of the log since when both are on the same segment, a full segment would also indicate a full log.
curunreservedpgs returns the number of free pages in the specified piece of disk. The third parameter (we provide here sysusages.unreservedpgs) is returned instead of the value in memory when the database is not opened i.e. not in use.
lct_admin with "logsegment_freepages" as the first parameter returns the number of free pages in a dedicated log segment
Excellent post. Waiting for long time. Thank you very much Henri !!!!!!!!!!!!!!
Error received:
Msg 3606, Level 16, State 4:
Server ‘E10’, Line 1:
Arithmetic overflow occurred.
My server’s version
1> select @@version
2> go
—————————————————————————————————————————————————————————————————————————————————————
Adaptive Server Enterprise/15.7/EBF 21771 SMP SP103 /P/RS6000/AIX 6.1/ase157sp101/3449/64-bit/FBO/Thu Sep 19 06:38:55 2013
Please help in correcting the code
It looks like a division by 0 is performed. This happens quite often with this kind of statements since Sybase tends to introduce new ways to compute this and makes old functions or columns return 0 instead of a value. Unfortunately, I currently do not have an ASE 15.7 installation on which I could test it. I’ll update the post if I can find one. If you manage to solve it inbetween, please post a comment. This would help other readers.
Hello,
The error is in data_size formula.
Looks like for some reason in 15.7, curunreservedpgs returns a value greater than u.size for some individual fragments.
Summing numerator and denominator independently solves the issue:
select db_name(d.dbid) as db_name
,ceiling(sum(case when u.segmap != 4 then u.size/1048576.*@@maxpagesize end )) as data_size
,ceiling((sum(case when u.segmap != 4 then u.size end) – sum(case when u.segmap != 4 then curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end))/1048576.*@@maxpagesize) as data_used
,ceiling(100 * (1 – 1.0 * sum(case when u.segmap != 4 then curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end) / sum(case when u.segmap != 4 then u.size end))) as data_used_pct
,ceiling(sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end)) as log_size
,ceiling(sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end) – lct_admin(“logsegment_freepages”,d.dbid)/1048576.*@@maxpagesize) as log_used
,ceiling(100 * (1 – 1.0 * lct_admin(“logsegment_freepages”,d.dbid) / sum(case when u.segmap in (4, 7) then u.size end))) as log_used_pct
from master..sysdatabases d, master..sysusages u
where u.dbid = d.dbid and d.status != 256
group by d.dbid
order by db_name(d.dbid)
Hope this helps.
Execellent query. I will use this for my ASE 15.7 servers!
Query works well, unless there are incorrect entries in sysusages. I have run into that error. Sybase automatically ignores the bad entries, but this script could be thrown off. By checking if the vdevno is >= 0 will make sure all counted devices are real / usable devices.
I changed the script like so:
select db_name(d.dbid) as db_name,
ceiling(sum(case when u.segmap != 4 and vdevno >= 0 then (u.size/1048576.)*@@maxpagesize end )) as data_size_MB,
ceiling(sum(case when u.segmap != 4 and vdevno >= 0 then size – curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end)/1048576.*@@maxpagesize) as data_used_MB,
ceiling(100 * (1 – 1.0 * sum(case when u.segmap != 4 and vdevno >= 0 then curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end) / sum(case when u.segmap != 4 then u.size end))) as data_used_pct,
ceiling(sum(case when u.segmap = 4 and vdevno >= 0 then u.size/1048576.*@@maxpagesize end)) as log_size_MB,
ceiling(sum(case when u.segmap = 4 and vdevno >= 0 then u.size/1048576.*@@maxpagesize end) – lct_admin(“logsegment_freepages”,d.dbid)/1048576.*@@maxpagesize) as log_used_MB,
ceiling(100 * (1 – 1.0 * lct_admin(“logsegment_freepages”,d.dbid) / sum(case when u.segmap in (4, 7) and vdevno >= 0 then u.size end))) as log_used_pct
from master..sysdatabases d, master..sysusages u
where u.dbid = d.dbid and d.status != 256
group by d.dbid
order by db_name(d.dbid)