存档
统计Sybase ASE数据库的数据段和日志段的剩余空间以及剩余率。以下SQL在ASE12.5.1及以上版本通过测试。
select convert(char(16),db_name(data_segment.dbid)) DBName
,str(round(total_data_pages / ((1024.0 * 1024) / @@maxpagesize),2),10,2) "Total Data(MB)"
,str(round(free_data_pages / ((1024.0 * 1024) / @@maxpagesize),2),10,2) "Free Data(MB)"
,str(round(total_log_pages / ((1024.0 * 1024) / @@maxpagesize),2),10,2) "Total Log(MB)"
,str(round(free_log_pages / ((1024.0 * 1024) / @@maxpagesize),2),10,2) "Free Log(MB)"
,str( round(100.0 * free_data_pages / total_data_pages ,2),10,2) "Free_Data%"
,str( round(100.0 * free_log_pages / total_log_pages,2),10,2) "Free_Log%"
from
(select dbid,sum(size) total_log_pages,lct_admin("logsegment_freepages", dbid ) free_log_pages
from master.dbo.sysusages
where segmap & 4 = 4
group by dbid
) log_segment
,
(select dbid,sum(size) total_data_pages ,sum(curunreservedpgs(dbid, lstart, unreservedpgs)) free_data_pages
from master.dbo.sysusages
where segmap <> 4
group by dbid
) data_segment
where data_segment.dbid = log_segment.dbid
--and data_segment.dbid > 3 and data_segment.dbid < 31513
order by data_segment.dbid
go
在公司测试环境上执行sp_spaceused时报error：1151的错误。虽然这不是一个严重的错误，记得好像以前也出现过一次，故记录一下。
1> sp_spaceused
2> go
database_name
database_size
----------------------------------------------------
----------------------------------------------------
ultranms
600.0 MB(1 row affected)
Msg 1151, Level 11, State 1:
Server 'ASE', Procedure 'sp_spaceused', Line 308:
Object 460577698 passed to OAM builtin function no longer exists in database 5.
Msg 1151, Level 11, State 1:
Server 'ASE', Procedure 'sp_spaceused', Line 308:
Object 460577698 passed to OAM builtin function no longer exists in database 5.
Msg 1151, Level 11, State 1:
Server 'ASE', Procedure 'sp_spaceused', Line 308:
Object 460577698 passed to OAM builtin function no longer exists in database 5.
Msg 1151, Level 11, State 1:
Server 'ASE', Procedure 'sp_spaceused', Line 308:
Object 460577698 passed to OAM builtin function no longer exists in database 5.
reserved data
index_size unused
-------------------------------- ----------------------------
---------------------------------------- ----------------------------
9020 KB 1396 KB
1064 KB 6560 KB
(return status = 0)
出现错误1151的原因是：执行sp_spaceused统计数据库的空间使用情况的时候，其它会话正在执行删除表的操作。
前面有篇文章中，介绍利用游标和sp_spaceused来查看数据库内所有表占用的空间，并倒序排序。
统计SQL SERVER数据库内所有表占用空间并排序
下面介绍另外一种方法：利用sp_MSforeachtable替代游标来实现上篇文章中的类似功能。
use ossdb
go--------查看所有表空间使用情况
CREATE TABLE #T(
name nvarchar(256),
rows varchar(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18)
)EXEC sp_MSforeachtable "INSERT #T EXEC sp_spaceused '?'"
SELECT * FROM #T ORDER BY Convert(int,SubString(data,1,Len(data)-3)) DESC
drop table #T
go
测试通过，将ossdb改成用户数据库名字即可。
在ASE中统计某个用户表的信息，比如：表中的行数、分配的空间、数据所占空间、索引所占空间以及剩余空间等，我们可以使用系统存储过程sp_spaceused来实现。
系统存储过程sp_spaceused有两个参数：@objname和@list_indices，第一个@objname是待统计的对象名，一般 是表名；第二个@list_indices标志是否单独统计索引信息，@list_indices=1表示单独统计索引信 息，@list_indices=0则不单独统计。
示例：
1> sp_spaceused sysobjects,0
2> go
name rowtotal reserved data index_size unused
---------- -------- -------- ------ ---------- ------
sysobjects 1014 252 KB 132 KB 68 KB 52 KB
(1 row affected)
(return status = 0)
1> sp_spaceused sysobjects,1
2> go
index_name size reserved unused
------------ ----- -------- ------
ncsysobjects 48 KB 64 KB 16 KB
csysobjects 20 KB 32 KB 12 KB
(1 row affected)
name rowtotal reserved data index_size unused
---------- -------- -------- ------ ---------- ------
sysobjects 1014 252 KB 132 KB 68 KB 52 KB
(return status = 0)
1>
但是利用sp_spaceused还不能实现统计某个库里面哪张表的数据记录数最多，哪张表占用的空间最大，哪些表的行数为0，以及哪些表的索引所占空间大于100M等等的问题。
如何实现呢？
方法一：改造sp_spaceused过程语法中的SQL语句。但是，sp_spaceused过程的源代码有560多行，看起来比较累，至少需要一个临时表存储中间临时数据。
方法二：是本博客专门推荐使用的，利用ASE中的代理表来实现。下面是操作步骤：
假设用户数据库名字为：andkylee
先执行：use andkylee
go
步骤一：建立代理表tablespace2
create existing table tablespace2(name varchar(128) null,rowtotal char(15) ,reserved char(15),[data] char(15),index_size char(15),unused char(15),_objname varchar(128) null,_list_indices int null) external procedure at "loopback.andkylee.dbo.sp_spaceused"
代理表的列 有：name,rowtotal,reserved,data,index_size,unused,_objname,_list_indices，其 中name,rowtotal,reserved,data,index_size,unused这几列接收存储过程sp_spaceused返回的结果 数据。
注意name,rowtotal,reserved,data,index_size,unused的各列的数据类型要和sp_spaceused返回的列的类型一致。
_objname,_list_indices 是过程sp_spaceused的参数列。
需要再sysservers中加入一个指向自己的远程服务器loopback。
sp_addserver loopback,null,@@servername
go
如果@@servername为空，则写dsedit中显示服务器名。
步骤二：在代理表tablespace2上建立视图，统计库内所有用户表的存储空间
create view TableSize(name,rowtotal,reserved,data_KBytes,index_size,unused)
as
select name,cast(str_replace(rowtotal,' KB','') as int),cast(str_replace(reserved,' KB','') as int),cast(str_replace(data,' KB','') as int),cast(str_replace(index_size,' KB','') as int),cast(str_replace(unused,' KB','') as int) from tablespace2 where _list_indices = 0
and _objname in
(
select user_name(uid)+"."+name from andkylee.dbo.sysobjects where type='U'
)
go
说明：可以使用其它的条件来仅仅统计某些表，比如：只统计用户test所拥有的表的空间信息，那么改为： where type='U' and uid=user_id("test")
步骤三： 针对视图TableSize，根据要求写SQL语句
比如：统计记录数最多的10个表的表名、行数、分配空间，用如下的SQL：
select top 10 name,rowtotal,reserved from TableSize order by rowtotal desc
go
1> select top 10 name,rowtotal,reserved from TableSize order by rowtotal desc
2> go
name
rowtotal reserved
-------------------------------------------------------------------------------
-------------------------------------------------
----------- -----------
SOS_Y_Hs_Xell
1044911 178308
test3
1008576 21940
test2
1008576 21992
SOS_Y_Wharehouse
380000 31488
SOS_Y_Hs_Xell_copy_1
288097 49596
FLOWREC1
285139 40580
PartitionTestTable
262144 11368
lzftest
262144 7496
SOS_Y_Hs_Xell_Back
236000 40512
SOS_R_Wharehouse
192000 38408
(10 rows affected)
根据自己的要求写针对TableSize的相应的SQL语句，上面的sql在此抛砖引玉。
备注：
1、由于每次统计过程中都要涉及到待统计表的存储空间大小的计算，所以，上面介绍的方法对系统性能会有一点小的影响。只要不是频繁的执行，个人感觉影响可以忽略。
2、另外针对数据库日志也就是系统表syslogs的统计结果和其它表的不一样。
1> sp_spaceused syslogs
2> go
name total_pages free_pages used_pages
reserved_pages
--------------- --------------- --------------- ---------------
---------------
syslogs 102400 101945 455
0
(1 row affected)
(return status = 0)
1>
所以，不要在上面的被统计表中包含syslogs表。
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：ASE sp_spaceused 代理表 存储过程 统计表的空间大小 行数最多的表的记录数
所占空间最大的表
————————————————————————————————-
在csdn论坛上看到有人问，如何获取指定数据库所在磁盘的磁盘剩余空间。
解决的思路是：通过sysfiles系统表找到数据库所属数据文件所在的物理路径，从数据文件的物理路径中提取所在磁盘盘符（第一个字母），最后用扩展存储过程xp_fixeddrives来获得磁盘驱动器的剩余空间(MB Bytes)。
SQL语句如下：
declare @drivename char(1)
select @drivename=left(filename,1) from sysfiles where fileid= 1
if not exists(select 1 from tempdb.dbo.sysobjects where name like '#FreeSpace%' and type='U')
create table #FreeSpace(
Drive char(1),
MB_Free int
)
else
truncate table #FreeSpace
insert into #FreeSpace
exec xp_fixeddrives
select MB_Free from #FreeSpace where Drive = @drivename
go
我的测试环境是的结果如下：
1> declare @drivename char(1)
2> select @drivename=left(filename,1) from sysfiles where fileid= 1
3>
4> if not exists(select 1 from tempdb.dbo.sysobjects where name like '#FreeSpace
%' and type='U')
5> create table #FreeSpace(
6> Drive char(1),
7> MB_Free int
8> )
9> else
10> truncate table #FreeSpace
11>
12> insert into #FreeSpace
13> exec xp_fixeddrives
14>
15> select MB_Free from #FreeSpace where Drive = @drivename
16> go(3 行受影响)
MB_Free
-----------
18905(1 行受影响)
1>
2>
3> select filename from sysfiles
4> go
filename--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------
D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdfD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
(2 行受影响)
1> xp_fixeddrives
2> go
drive MB 可用空间
----- -----------
C 26562
D 18905
E 22517(3 行受影响)
上面的sql语句和csdn上帖子的发帖人要求的有点差距，
第一：指定数据库，这点很容易。直接select filename from <your_db_name>.dbo.sysfiles即可。
第二：某个数据库的数据文件可能跨越两个及以上不同的物理磁盘；所以@drivename变量有可能得到不唯一的值。可以考虑用游标来实现。
————————————————————————————————-
—- 本文为andkylee个人原创，请在尊重作者劳动成果的前提下进行转载；
—- 转载务必注明原始出处 : http://www.dbainfo.net
—- 关键字：sqlcmd sqlserver2005 sysfiles xp_fixeddrives 磁盘剩余空间
————————————————————————————————-