ASE15.x之前版本中查看设备剩余空间以及设备上数据库分配信息的存储过程

发表评论

在ASE15.x之前的所有版本中（包括ASE12.5.4），存储过程sp_helpdevcie无法显示设备的剩余空间以及设备上各个数据库的具体分配情况。

比如：在ASE12.5中，执行sp_helpdevice master的结果为：

1> sp_helpdevice master
2> go
 device_name                    physical_name
         description
         status cntrltype device_number
         low
         high
----------------------------------------------------------------------------------------------------------------
 master                         e:\sybase125\data\master.dat
         special, dsync on, default disk, physical disk, 100.00 MB
              3         0             0
                                                                                        0
                                                                                    51199

(1 row affected)
(return status = 0)

上面输出结果中加粗标记出来的100.00MB表示master设备的总大小。至于master设备还剩余多少空间？master设备都分配给哪些数据库使用了？ASE15.x之前版本的存储过程sp_helpdevice不能给出答案。

在ASE15.0之后版本的系统存储过程sp_helpdevice增加了上述功能。比如：ASE15.0.3中执行sp_helpdevice master的结果如下：

1> sp_helpdevice master
2> go
 device_name physical_name
         description
         status cntrltype vdevno vpn_low vpn_high
-------------------------------------------------------------------------------------
         ------ --------- ------ ------- --------
 master      D:\sybase\data\master.dat
         file system device, special, MIRROR ENABLED, mirror = 'D:\sybase\data\master_mirr.dat', serial writes, dsync on, directio off, reads mirrored,
          default disk, physical disk, 80.00 MB, Free: 42.00 MB
            739         0      0       0    40959

(1 row affected)
 dbname      size          allocated           vstart lstart
 ----------- ------------- ------------------- ------ ------
 master           26.00 MB Dec  2 2009  6:58PM      4      0
 model             6.00 MB Dec  2 2009  6:58PM  13316      0
 sybsystemdb       6.00 MB Dec  2 2009  6:58PM  19460      0

(1 row affected)
(return status = 0)

上面的结果包含master设备的剩余空间：42MB，以及master设备上分配了master，model，sybsystemdb三个系统库，以及相应的数据库大小、分配日期等等。

那么，如何在ASE15.x之前的版本中查看设备的剩余空间以及设备上的数据库分配信息呢？

方法有两种：

1.使用sybase central这个sybase ASE自带的客户端工具。个人感觉ASE15以来自带的Sybase Central功能比较丰富，更加易用了。

在ASE12.5自带的Sybase Central上查看各设备的剩余空间：

ASE12.5 上使用sybase central查看数据库各设备的总空间、剩余空间信息

在master设备上点右键，选择“属性”，切换到databases能够看到在master设备上各个数据库所分配的空间。

查看master设备上所分配的所有数据库

2.自己编写一个存储过程，比如：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 平台上进行了测试。

/*
* 此存储过程sp_helpdevice2适用于 ASE v11.x, v12.x,不能用于ASE15.x。实际上ASE15.x中的sp_helpdevice包含设备剩余空间以及设备上所分配的数据库的功能！
* ASE v11.x版本中系统表 sysusages中没有crdate这个表示设备段分配时间的字段，考虑到支持ASEv11.x，为了简单处理，没有在Allocation information 中列出设备段的具体分配时间！
*/

/*
* 此存储过程在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

 

存储过程sp_helpdevice2的语法在此下载：sp_helpdevice2

使用存储过程sp_helpdevice2的输出结果见下图所示：

存储过程sp_helpdevice2的执行效果

最后，存储过程sp_helpdevice2仅输出了一些基本信息，像设备的状态信息等还请结合自带的sp_helpdevice进行查看。

在chinaunix的sybase板块有讨论此博文主题的帖子：怎么查看设备文件上还有多少空间没有被分配出去

有什么建议欢迎在博文后面留言！

--更新： 2021-09-08

增加显示设备ID，数据库ID，设备内偏移页号。

use sybsystemprocs
go

if exists(select 1 from dbo.sysobjects where type='P' and name='sp_helpdevice2')
        drop procedure sp_helpdevice2
go

/*
* 此存储过程在ASE v11.0.1, ASE v11.5.1, ASE v11.9.2, ASE v12.0, ASE v12.5, v12.5.0.3, v12.5.2, v12.5.4 平台测试通过！适用于 ASE v11.x, v12.x,不能用于ASE15。实际上ASE15.x中的sp_helpdevice完全能够实现该功能！
* ASE v11.x及ASE v12.0版本中系统表 sysusages中没有crdate这个表示设备段分配时间的字段，考虑到支持ASE v11.x为了简单处理，没有在Database Allocation information 中列出设备段的具体分配时间！
* ASE v11.x及ASE v12.0只支持2K页面，没有全局变量@@maxpagesize，需要手工替换本存储过程源码中的@@maxpagesize为@@pagesize！
*/
create procedure sp_helpdevice2
        @devname varchar(30) = "%"
as

declare @numpgsmb float
declare @numpgsmb2 float
declare @Major_Version int
declare @Page2KMulti int

set nocount on
--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

select @numpgsmb = (1048576. / @@pagesize)
select @numpgsmb2 = (1048576. / @@maxpagesize)
select @Page2KMulti = @@maxpagesize / 2048

/*  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 DevNo = d.low / power(2, 24),
            DevName = d.name,
            TotalSize = str(#totalsize.totalsizeMB, 10, 2),
            UsedSize = str(#usedsize.usedsizeMB, 10, 2),
            FreeSize = str(#totalsize.totalsizeMB - #usedsize.usedsizeMB, 10, 2),
            PhysName = convert(varchar(100), 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 "========================== Database Allocation 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 u.dbid, dbname = db_name(u.dbid),
                "size(MB)"=str(u.size / @numpgsmb2, 10, 2),
                u.vstart,
                (u.vstart  - d.low) / @Page2KMulti as offsetpage,
                u.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 u.vstart
end

drop table #totalsize
drop table #usedsize
go

/* grant the execute privilege to public */
grant execute on sp_helpdevice2 to public
go
 

如果Sybase ASE 版本为v11.x及v12.0，只支持2K页面，没有全局变量@@maxpagesize，需要手工替换本存储过程源码中的@@maxpagesize为@@pagesize！

use sybsystemprocs
go

if exists(select 1 from dbo.sysobjects where type='P' and name='sp_helpdevice2')
        drop procedure sp_helpdevice2
go

/*
* 此存储过程在ASE v11.0.1, ASE v11.5.1, ASE v11.9.2, ASE v12.0, ASE v12.5, v12.5.0.3, v12.5.2, v12.5.4 平台测试通过！适用于 ASE v11.x, v12.x,不能用于ASE15。实际上ASE15.x中的sp_helpdevice完全能够实现该功能！
* ASE v11.x及ASE v12.0版本中系统表 sysusages中没有crdate这个表示设备段分配时间的字段，考虑到支持ASE v11.x为了简单处理，没有在Database Allocation information 中列出设备段的具体分配时间！
* ASE v11.x及ASE v12.0只支持2K页面，没有全局变量@@maxpagesize，需要手工替换本存储过程源码中的@@maxpagesize为@@pagesize！
*/
create procedure sp_helpdevice2
        @devname varchar(30) = "%"
as

declare @numpgsmb float
declare @numpgsmb2 float
declare @Major_Version int
declare @Page2KMulti int

set nocount on
--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

select @numpgsmb = (1048576. / @@pagesize)
select @numpgsmb2 = (1048576. / @@pagesize)
select @Page2KMulti = @@pagesize / 2048

/*  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 DevNo = d.low / power(2, 24),
            DevName = d.name,
            TotalSize = str(#totalsize.totalsizeMB, 10, 2),
            UsedSize = str(#usedsize.usedsizeMB, 10, 2),
            FreeSize = str(#totalsize.totalsizeMB - #usedsize.usedsizeMB, 10, 2),
            PhysName = convert(varchar(100), 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 "========================== Database Allocation 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 u.dbid, dbname = db_name(u.dbid),
                "size(MB)"=str(u.size / @numpgsmb2, 10, 2),
                u.vstart,
                (u.vstart  - d.low) / @Page2KMulti as offsetpage,
                u.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 u.vstart
end

drop table #totalsize
drop table #usedsize
go

/* grant the execute privilege to public */
grant execute on sp_helpdevice2 to public
go
 

 

—————————————————————————————————-

本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；

—- 转载务必注明原始出处 : http://www.dbainfo.net

—- 关键字：  ASE15之前版本 设备剩余空间 sp_helpdevice2

—————————————————————————————————-

  • 本文链接地址：https://www.dbainfo.net/get-free-space-of-device-and-database-allocation-prior-to-ase12-5.htm；
  • 本文为dbainfo个人原创，请在尊重作者劳动成果的前提下进行转载；
  • 转载务必注明原始出处 : Sybase数据库技术，数据库恢复专家；
  • 对《ASE15.x之前版本中查看设备剩余空间以及设备上数据库分配信息的存储过程》有何疑问或见解，请在本文下方发表；
  • 对网站还有其他问题或建议，请提交在留言板，谢谢！
    • 标签: ,
    目前还没有任何评论.
    :wink: :twisted: :roll: :oops: :mrgreen: :lol: :idea: :evil: :cry: :arrow: :?: :-| :-x :-o :-P :-D :-? :) :( :!: 8-O 8)