- 论坛徽章:
- 6
|
本帖最后由 andkylee 于 2011-08-26 13:58 编辑
回复 6# yiguihuo
楼主的问题很清楚、明白。 在平常运维中，我也遇到和楼主一样的问题。执行：sp_helpdevice 设备名 的时候不能查看到设备剩余空间以及具体分配给了哪些数据库。
通过查看ase12.5.4中过程sp_helpdevice的语法，发现：ase15之前的所有版本都不支持上述功能，只有ase15版本后才会显示剩余空间以及分配信息。
既然楼主都使用sp_helpdevice上万遍了，且楼主发现sybase central等图形工具支持该功能，那楼主为什么不去研究一下这些工具是怎么实现的呢？
很早之前就想写一个能查询设备剩余空间的存储工程，现在恰好楼主问这个问题。就写了一个能够查看设备总空间以及剩余空间，还有设备上具体分配信息的存储过程sp_helpdevice2
我参考了ASE12.5.4和ASE15.0.3中的sp_helpdevice的语法完成该过程sp_helpdevice2的编写。分别在ASE v11.0.1, ASE v11.5.1， ASE v11.9.2, ASE v12.5, v12.5.0.3, v12.5.4 平台上进行了测试。
/*
* 此存储过程在ASE v11.0.1, ASE v11.5.1, ASE v11.9.2, ASE v12.5, v12.5.0.3, v12.5.4 平台测试通过！适用于 ASE v11.x, v12.x,不能用于ASE15。实际上ASE15.x中的sp_helpdevice完全能够实现该功能！
* ASE v11.x版本中系统表 sysusages中没有crdate这个表示设备段分配时间的字段，考虑到支持ASEv11.x为了简单处理，没有在Allocation information 中列出设备段的具体分配时间！
*/
use sybsystemprocs
go
if exists(select 1 from dbo.sysobjects where type='P' and name='sp_helpdevice2')
drop procedure sp_helpdevice2
go
create procedure sp_helpdevice2
@devname varchar(30) = "%"
as
declare @numpgsmb float
declare @numpgsmb2 float
declare @Major_Version int
set nocount on
select @numpgsmb = (1048576. / @@pagesize)
select @numpgsmb2 = (1048576. / @@maxpagesize)
--select @version_as_num = @@version_as_integer
select @Major_Version= convert(int, right(substring(@@version,1,charindex('.',@@version)-1),2) )
if @Major_Version >= 15 or @Major_Version < 11
begin
print "this procedure is available for ASE versions from v11.x to v12.5.x, not for ASE15.x!"
return (1)
end
/* See if the device exists.*/
if not exists (select *
from master.dbo.sysdevices
where name like @devname)
begin
/* 17610, "No such i/o device exists." */
raiserror 17610
return (1)
end
/* total size of device */
select d.name,
totalsizeMB = (1. + (d.high - d.low)) / @numpgsmb
into #totalsize
from master.dbo.sysdevices d
where d.status & 2 = 2
and name like @devname
group by d.name
/* Calculate used size in MB */
select d.name,
usedsizeMB = isnull(sum(u.size) / @numpgsmb2,0)
into #usedsize
from master.dbo.sysdevices d, master.dbo.sysusages u
where u.vstart >= d.low and u.vstart <= d.high
and d.status & 2 = 2
and d.name like @devname
group by d.name
union
select d.name, 0.
from master.dbo.sysdevices d
where not exists ( select 1 from master.dbo.sysusages u where u.vstart >= d.low and u.vstart <= d.high )
and d.status & 2 = 2
and d.name like @devname
set nocount off
/* Calculate the free size of device */
select d.name ,TotalSize = str(#totalsize.totalsizeMB,10,2), UsedSize = str(#usedsize.usedsizeMB,10,2),FreeSize = str(#totalsize.totalsizeMB - #usedsize.usedsizeMB,10,2),phyname = convert(varchar(50),d.phyname)
from master.dbo.sysdevices d, #totalsize, #usedsize
where d.name = #totalsize.name
and #totalsize.name = #usedsize.name
order by low,high
if (select count(*) from master.dbo.sysdevices where name like @devname) = 1
begin
print ""
print "========================== Allocate Information =========================="
/*if @Major_Version = 12
select dbname = db_name(dbid), "size(MB)"=str(size/@numpgsmb2,10,2), allocated = u.crdate, vstart, lstart
from master.dbo.sysusages u, master.dbo.sysdevices d
where d.status & 2 = 2
and d.name like @devname
and (u.vstart >= d.low and u.vstart <= d.high )
order by dbname,vstart
else if @Major_Version = 11
*/
select dbname = db_name(dbid), "size(MB)"=str(size/@numpgsmb2,10,2), vstart, lstart
from master.dbo.sysusages u, master.dbo.sysdevices d
where d.status & 2 = 2
and d.name like @devname
and (u.vstart >= d.low and u.vstart <= d.high )
order by dbname,vstart
end
drop table #totalsize
drop table #usedsize
go
/* grant the execute privilege to public */
grant execute on sp_helpdevice2 to public
go |
|
免费注册
发表于 2011-08-24 11:10
发表于 2011-08-30 14:37
