在Sybase中一次获得所有数据库的数据库使用情况
在Sybase中一次获得所有数据库的数据库使用情况
提问于 2014-07-21 09:38:33
如何一次获得所有数据库的数据库使用情况？
sp_spaceused和sp_helpdb <dbname>一次只给出一个用法。
我使用Sybase中央客户端。
回答 3
Database Administration用户
发布于 2015-08-11 01:22:53
下面是我使用的脚本-- master..sysdatabases& master..sysusages
--- Source : http://benohead.com/sybase-size-of-data-and-log-segments-for-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 not in (256,4096)
group by d.dbid
order by db_name(d.dbid)Database Administration用户
发布于 2014-07-29 21:30:03
您可能应该创建一个可以运行并返回多个命令的值的.sql脚本。
可以使用以下格式轻松创建脚本：
set nocount on
select "sp_helpdb " + name + char(10) + "exec " + name + "..sp_spaceused" + char(10) + "go"
go这将打印如下所示的命令列表：
sp_helpdb DATABASE_01
exec DATABASE..sp_spaceused
go
sp_helpdb DATABASE_02
exec DATABASE..sp_spaceused
go
...这个输出可以被捕获到一个脚本文件中，然后一次性执行。
Database Administration用户
发布于 2017-12-20 19:12:47
Kin的查询运行良好，除非sysusages中有不正确的条目。我遇到了那个错误。Sybase会自动忽略坏条目，但是这个脚本可能会被抛出。通过检查vdevno是否是>=，0将确保所有计数的设备都是真实的/可用的设备。
我修改了脚本如下：
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)页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/71954
复制相关文章
相似问题
腾讯云开发者
