MSSQL 监控数据/日志文件增长实现方法

前几天,在所有数据库服务器部署了监控磁盘空间的存储过程和作业后（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/