前几天,在所有数据库服务器部署了监控磁盘空间的存储过程和作业后（ms sql 监控磁盘空间告警），今天突然收到了两封告警邮件，好吧，存储规划是一方面，但是，是不是要分析一下是什么原因造成磁盘空间不足的呢？会不会是因为突然暴增的日志文件，抑或是系统业务猛增导致数据量暴增，还是历史数据累计原因….分析总得有数据来支撑吧，但是现在只有那些数据文件的当前大小信息，没有数据文件的历史增长变化信息，所以，今天就想实现这么一个功能，每天（频率可以调整）去收集一下数据文件的信息，放到一个表里面，这样方便我们分析数据文件的增长演变例程，甚至你可以将数据文件的增长幅度和业务变化关联起来分析….
那么接下来就是我的设计思路和实现代码，目前只是简单实现，以后将继续优化,丰富一些功能。
首先我们创建一个表diskcapacityhistory，用来保存数据库文件的历史增长变化信息：
复制代码 代码如下:
use msdb;
go
if exists (select 1 from dbo.sysobjects where id = object_id(n”) and xtype=’u’)
drop table diskcapacityhistory;
go
create table dbo.diskcapacityhistory
(
[date_cd] int ,
[databaseid] int ,
[fileid] int ,
[databasename] sysname ,
[logicalname] varchar(32) ,
[filetypedesc] nvarchar(60) ,
[physicalname] nvarchar(260) ,
[statedesc] nvarchar(60) ,
[maxsize] nvarchar(32) ,
[growthtype] nvarchar(8) ,
[isreadonly] int ,
[ispercentgrowth] smallint ,
[size] float ,
[growth_mom_rat] float ,
[growth_yoy_rat] float ,
constraint pk_diskcapacityhistory primary key(date_cd, databaseid, fileid)
);
复制代码 代码如下:
exec sys.sp_addextendedproperty @name = n’ms_description’
, @value = ‘日期编码’
, @level0type = n’schema’
, @level0name = n’dbo’
, @level1type = n’table’
, @level1name = n’diskcapacityhistory’
, @level2type = n’column’
, @level2name = n’date_cd’;
exec sys.sp_addextendedproperty @name = n’ms_description’
, @value = ‘数据库标识’
, @level0type = n’schema’
, @level0name = n’dbo’
, @level1type = n’table’
, @level1name = n’diskcapacityhistory’
, @level2type = n’column’
, @level2name = n’databaseid’;
exec sys.sp_addextendedproperty @name = n’ms_description’
, @value = ‘文件标识’
, @level0type = n’schema’
, @level0name = n’dbo’
, @level1type = n’table’
, @level1name = n’diskcapacityhistory’
, @level2type = n’column’
, @level2name = n’fileid’;
exec sys.sp_addextendedproperty @name = n’ms_description’
, @value = ‘数据库名称’
, @level0type = n’schema’
, @level0name = n’dbo’
, @level1type = n’table’
, @level1name = n’diskcapacityhistory’
, @level2type = n’column’
, @level2name = n’databasename’;
exec sys.sp_addextendedproperty @name = n’ms_description’
, @value = ‘数据库逻辑名称’
, @level0type = n’schema’
, @level0name = n’dbo’
, @level1type = n’table’
, @level1name = n’diskcapacityhistory’
, @level2type = n’column’
, @level2name = n’logicalname’;
exec sys.sp_addextendedproperty @name = n’ms_description’
, @value = ‘文件类型描述’
, @level0type = n’schema’
, @level0name = n’dbo’
, @level1type = n’table’
, @level1name = n’diskcapacityhistory’
, @level2type = n’column’
, @level2name = n’filetypedesc’;
exec sys.sp_addextendedproperty @name = n’ms_description’
, @value = ‘物理数据库文件’
, @level0type = n’schema’
, @level0name = n’dbo’
, @level1type = n’table’
, @level1name = n’diskcapacityhistory’
, @level2type = n’column’
, @level2name = n’physicalname’;
exec sys.sp_addextendedproperty @name = n’ms_description’
, @value = ‘文件最大大小’
, @level0type = n’schema’
, @level0name = n’dbo’
, @level1type = n’table’
, @level1name = n’diskcapacityhistory’
, @level2type = n’column’
, @level2name = n’maxsize’;
exec sys.sp_addextendedproperty @name = n’ms_description’
, @value = ‘文件增长类型’
, @level0type = n’schema’
, @level0name = n’dbo’
, @level1type = n’table’
, @level1name = n’diskcapacityhistory’
, @level2type = n’column’
, @level2name = n’growthtype’;
exec sys.sp_addextendedproperty @name = n’ms_description’
, @value = ‘是否只读类型’
, @level0type = n’schema’
, @level0name = n’dbo’
, @level1type = n’table’
, @level1name = n’diskcapacityhistory’
, @level2type = n’column’
, @level2name = n’isreadonly’;
exec sys.sp_addextendedproperty @name = n’ms_description’
, @value = ‘是否按百分比增长’
, @level0type = n’schema’
, @level0name = n’dbo’
, @level1type = n’table’
, @level1name = n’diskcapacityhistory’
, @level2type = n’column’
, @level2name = n’ispercentgrowth’;
exec sys.sp_addextendedproperty @name = n’ms_description’
, @value = ‘数据文件大小(gb)’
, @level0type = n’schema’
, @level0name = n’dbo’
, @level1type = n’table’
, @level1name = n’diskcapacityhistory’
, @level2type = n’column’
, @level2name = n’size’;
exec sys.sp_addextendedproperty @name = n’ms_description’
, @value = ‘文件增长环比(%)’
, @level0type = n’schema’
, @level0name = n’dbo’
, @level1type = n’table’
, @level1name = n’diskcapacityhistory’
, @level2type = n’column’
, @level2name = n’growth_mom_rat’;
exec sys.sp_addextendedproperty @name = n’ms_description’
, @value = ‘文件增长同比(%)’
, @level0type = n’schema’
, @level0name = n’dbo’
, @level1type = n’table’
, @level1name = n’diskcapacityhistory’
, @level2type = n’column’
, @level2name = n’growth_yoy_rat’;
go
if object_id(n’sp_diskcapacity_cal’) is not null
drop procedure sp_diskcapacity_cal;
go
接下来，我们创建存储过程，负责来收集、统计这些数据库的文件的相关信息。关于环比/同比,正常情况一般是:
环比: (指标当前值 – 指标值(上个月同一天))/ 指标值(上个月同一天) 。
同比: (指标当前值 – 指标值(去年月同一天))/ 指标值(去年月同一天) 。
其实如果关注每天的数据文件变化情况，这个代码里面的环比、同比其实意义不大，其实我们可以这样定义环比、同比：
环比： （指标当前值 – 指标值（昨天）)/指标值（昨天）。
同比： （指标当前值 – 指标值 （上个月））/指标值（上个月）
当然，你也可以把这四个指标都加上，对比参考，侧重点不同而已。
复制代码 代码如下:
if object_id(n’sp_diskcapacity_cal’)is not null
drop procedure sp_diskcapacity_cal;
go
create procedure dbo.sp_diskcapacity_cal
as
begin
insert into dbo.diskcapacityhistory
(
[date_cd] ,
[databaseid] ,
[fileid] ,
[databasename] ,
[logicalname] ,
[filetypedesc] ,
[physicalname] ,
[statedesc] ,
[maxsize] ,
[growthtype] ,
[isreadonly] ,
[ispercentgrowth] ,
[size]
)
select cast(replace(convert(varchar(10),getdate(),120),’-‘,”) as int)
as datecd ,
database_id as databaseid ,
file_id as fileid ,
db_name(database_id) as databasename ,
name as logicalname ,
type_desc as filetypedesc ,
physical_name as physicalname ,
state_desc as statedesc ,
case when max_size = 0 then n’不允许增长’
when max_size = -1 then n’自动增长’
else ltrim(str(max_size * 8.0 / 1024 / 1024, 14, 2)) + ‘g’
end as maxsize ,
case when is_percent_growth = 1
then rtrim(cast(growth as char(10))) + ‘%’
else rtrim(cast(growth as char(10))) + ‘m’
end as growth ,
is_read_only as isreadonly ,
is_percent_growth as ispercentgrowth ,
cast(size * 8.0 / 1024 / 1024 as decimal(8, 4)) as size
from sys.master_files;
merge into dbo.diskcapacityhistory dm using
(
select m.date_cd ,
m.databaseid ,
m.fileid ,
case when n.size is null or n.size = 0 then 0 else
(m.size – n.size)/n.size end as growth_mom_rat
from dbo.diskcapacityhistory m
left join dbo.diskcapacityhistory n on
cast(cast(m.date_cd as char(8)) as date) = dateadd(month, 1, cast(cast(n.date_cd as char(8)) as date))
and m.databaseid = n.databaseid and m.fileid = n.fileid
where m.date_cd = cast(replace(convert(varchar(10),getdate(),120),’-‘,”) as int)
) tmp
on
(
dm.date_cd = tmp.date_cd and
dm.databaseid = tmp.databaseid and
dm.fileid = tmp.fileid
)
when matched then update set
dm.growth_mom_rat = tmp.growth_mom_rat;
end
go
顺便吐槽一下：由于前两年一直使用oracle数据库，很少接触sql server，在实现上面功能的时候，我深深的体会到了oracle和sql server的巨大差距，如果用pl/sql实现，那非常方便快捷，但是用t-sql让我遇到了几个相当痛苦地方，下面顺便记录对比一下吧：
一：由于我采用int来保存日期数据，那么需要在date类型和int类型之间转换，我们来对比一下两者的差别吧：
1.1 date类型转换为整型:
t-sql：
select cast(replace(convert(varchar(10),getdate(),120),’-‘,”) as int);
pl/sql：
select to_char(date_cd, ‘yyyymmdd’) from dual;
1.2 整型转换为date类型（字段date_cd）
t-sql：
select cast(cast(date_cd as char(8)) as date) from test;
pl/sql：
select to_date(date_cd, ‘yyyy-mm-dd’) from test;
结论: 纯属个人感受,从上面的脚本的简单性,方便性上,感觉oracle完胜sql server
二：计算数据文件增长同比、环比值
1：sql server 2005 没有merge语句功能，上面的脚本得改写成
复制代码 代码如下:
updatedbo.diskcapacityhistory
set growth_mom_rat =( selectcase when n.size is null
or n.size = 0 then 0
else ( dbo.diskcapacityhistory.size
– n.size ) / n.size
end as growth_mom_rat
from dbo.diskcapacityhistory n
where cast(cast(dbo.diskcapacityhistory.date_cd as char(8)) as date) = dateadd(month,
1,
cast(cast(n.date_cd as char(8)) as date))
and dbo.diskcapacityhistory.databaseid = n.databaseid
and dbo.diskcapacityhistory.fileid = n.fileid
)
wheredbo.diskcapacityhistory.date_cd = cast(replace(convert(varchar(10), getdate(), 120),
‘-‘, ”) as int)
updatedbo.diskcapacityhistory
set growth_yoy_rat =( selectcase when n.size is null
or n.size = 0 then 0
else ( dbo.diskcapacityhistory.size
– n.size ) / n.size
end as growth_yoy_rat
from dbo.diskcapacityhistory n
where cast(cast(dbo.diskcapacityhistory.date_cd as char(8)) as date) = dateadd(month,
12,
cast(cast(n.date_cd as char(8)) as date))
and dbo.diskcapacityhistory.databaseid = n.databaseid
and dbo.diskcapacityhistory.fileid = n.fileid
)
wheredbo.diskcapacityhistory.date_cd = cast(replace(convert(varchar(10), getdate(), 120),
‘-‘, ”) as int)
或
复制代码 代码如下:
create table #diskcapacityhistory
(
date_cd int ,
databaseid int ,
fileid int ,
growth_mom_rat float
) ;
insertinto #diskcapacityhistory
select m.date_cd ,
m.databaseid ,
m.fileid ,
case when n.size is null
or n.size = 0 then 0
else ( m.size – n.size ) / n.size
end as growth_mom_rat
from dbo.diskcapacityhistory m ,
dbo.diskcapacityhistory n
where cast(cast(m.date_cd as char(8)) as date) = dateadd(month, 1,
cast(cast(n.date_cd as char(8)) as date))
and m.databaseid = n.databaseid
and m.fileid = n.fileid
and m.date_cd = cast(replace(convert(varchar(10), getdate()
– 1, 120), ‘-‘, ”) as int)
update dbo.diskcapacityhistory
set growth_mom_rat = m.growth_mom_rat
from #diskcapacityhistory m
where dbo.diskcapacityhistory.date_cd = m.date_cd
and dbo.diskcapacityhistory.databaseid = m.databaseid
and dbo.diskcapacityhistory.fileid = m.fileid ;
2: 幸好sql 2008还把oracle的merge的功能给模仿了过来，但是t-sql缺少oracle数据库强大的分析函数lag，如果有这个，我计算环比，同比就非常方便了，一个sql就搞定了，下面是个例子，本想把oracle的sql也做个例子展现，但是又要建表、造数，折腾起来比较麻烦。
复制代码 代码如下:
merge into dm.tm_wggbo_idctobusvoldtl_day dm
using (
select *
from (
select date_cd,
city_id,
idc_node,
vol_type,
lag(idc_vol_rat ) over(partition by city_id,idc_node,vol_type,substr(date_cd,7,2) order by substr(date_cd,0,6)) as idc_mom_rat ,
lag(idc_vol_rat ) over(partition by city_id,idc_node,vol_type,substr(date_cd,5,4) order by substr(date_cd,0,4)) as idc_yoy_rat ,
from dm.tm_wggbo_idctobusvoldtl_day
) t
where exists(select 1 from etl.t_idcvol_day_${ssid} where date_cd = t.date_cd)
) temp
on (
dm.date_cd = temp.date_cd and
dm.city_id = temp.city_id and
dm.idc_node = temp.idc_node and
dm.vol_type = temp.vol_type
)
when matched then
update
set dm.idc_mom_rat = temp.idc_mom_rat ,
dm.idc_yoy_rat = temp.idc_yoy_rat
commit;
作者：潇湘隐者
出处：http://www.cnblogs.com/kerrycode/