音效素材网提供各类素材，打造精品素材网站！

站内导航 站长工具 投稿中心 手机访问

音效素材

热门搜索： 狗叫声 打雷 钟声 wordpress模板 图标素材
网页制作|编程语言|产品运营|平面设计|操作系统|数据库|服务器|
当前位置：首页 > 教程 > 数据库 > MsSql > MSSQL 监控数据/日志文件增长实现方法

MSSQL 监控数据/日志文件增长实现方法
日期：2013-08-04 22:30:29   来源：脚本之家

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

    日志监控数据文件增长
    上一篇：SQL排除重复结果只取字段最大值分享
    下一篇：Replace关键字的妙用查询是否包含某个特定字符串

    您感兴趣的教程

    在docker中安装mysql详解

    本篇文章主要介绍了在docker中安装mysql详解，小编觉得挺不错的，现在分享给大家，也给大家做个参考。一起跟随小编...

    详解 安装 docker mysql

    win10中文输入法仅在桌面显示怎么办?

    win10中文输入法仅在桌面显示怎么办?

    win10系统使用搜狗，QQ输入法只有在显示桌面的时候才出来，在使用其他程序输入框里面却只能输入字母数字，win10中...

    win10 中文输入法

    一分钟掌握linux系统目录结构

    这篇文章主要介绍了linux系统目录结构，通过结构图和多张表格了解linux系统目录结构，感兴趣的小伙伴们可以参考一...

    结构 目录 系统 linux

    PHP程序员玩转Linux系列 Linux和Windows安装

    这篇文章主要为大家详细介绍了PHP程序员玩转Linux系列文章，Linux和Windows安装nginx教程，具有一定的参考价值，感兴趣...

    玩转 程序员 安装 系列 PHP

    win10怎么安装杜比音效Doby V4.1 win10安装杜

    第四代杜比®家庭影院®技术包含了一整套协同工作的技术，让PC 发出清晰的环绕声同时第四代杜比家庭影院技术...

    win10杜比音效

    纯CSS实现iOS风格打开关闭选择框功能

    这篇文章主要介绍了纯CSS实现iOS风格打开关闭选择框,本文通过实例代码给大家介绍的非常详细，对大家的学习或工作...

    css ios c

    Win7如何给C盘扩容 Win7系统电脑C盘扩容的办法

    Win7如何给C盘扩容 Win7系统电脑C盘扩容的

    Win7给电脑C盘扩容的办法大家知道吗？当系统分区C盘空间不足时，就需要给它扩容了，如果不管，C盘没有足够的空间...

    Win7 C盘 扩容

    百度推广竞品词的投放策略

    SEM是基于关键词搜索的营销活动。作为推广人员，我们所做的工作，就是打理成千上万的关键词，关注它们的质量度...

    百度推广 竞品词

    Visual Studio Code(vscode) git的使用教程

    这篇文章主要介绍了详解Visual Studio Code(vscode) git的使用，小编觉得挺不错的，现在分享给大家，也给大家做个参考。...

    教程 Studio Visual Code git

    七牛云储存创始人分享七牛的创立故事与

    这篇文章主要介绍了七牛云储存创始人分享七牛的创立故事与对Go语言的应用,七牛选用Go语言这门新兴的编程语言进行...

    七牛 Go语言

    Win10预览版Mobile 10547即将发布 9月19日上午

    微软副总裁Gabriel Aul的Twitter透露了 Win10 Mobile预览版10536即将发布，他表示该版本已进入内部慢速版阶段，发布时间目...

    Win10 预览版

    HTML标签meta总结,HTML5 head meta 属性整理

    移动前端开发中添加一些webkit专属的HTML5头部标签，帮助浏览器更好解析HTML代码，更好地将移动web前端页面表现出来...

    移动端html5模拟长按事件的实现方法

    这篇文章主要介绍了移动端html5模拟长按事件的实现方法的相关资料,小编觉得挺不错的，现在分享给大家，也给大家...

    移动端 html5 长按

    HTML常用meta大全(推荐)

    这篇文章主要介绍了HTML常用meta大全(推荐)，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参...

    cdr怎么把图片转换成位图? cdr图片转换为位图的教程

    cdr怎么把图片转换成位图? cdr图片转换为

    cdr怎么把图片转换成位图？cdr中插入的图片想要转换成位图，该怎么转换呢？下面我们就来看看cdr图片转换为位图的...

    cdr 图片 位图

    win10系统怎么录屏?win10系统自带录屏详细教程

    win10系统怎么录屏?win10系统自带录屏详细

    当我们是使用win10系统的时候，想要录制电脑上的画面，这时候有人会想到下个第三方软件，其实可以用电脑上的自带...

    win10 系统自带录屏 详细教程

    + 更多教程 +
    精选教程
    教程标签
    精妙的SQL语句 验证SQL保留字 数据类型与 将Sql 经典语句 全文检索技术 未公开的SQL Server日期计算 inner right 2000的安全配置 SQL语句示例 删除重复记录 收缩日志 Server基本函数 MSSQL经典语句 删除的实现 复习一下sql SpaceUsed 使用MSSQL外联接 如何创建SQL 标识 显式值 Transact_SQL 小手册 清空MSSQL日志 与set recovery 2005 临时表 数据临时表 EXISTS替代NOT IN的语句 MDF文件在SQL 更改SQL T-SQL 设定sql 格式转换方式 推荐SQL 简单的SQL

    关于我们

    音效素材网整合国内外优质的资源，内容涉及js效果代码，平面设计教程，网页设计教程，cms建站，网页模版等领域，提供最新的行业资讯，设计教程和素材参考学习。

    版权声明

    声明：本站发布的资源均来自互联网，仅供交流学习之用，请勿作商业用途，所有资源版权归原作者所有。如果有侵犯到您的权益，请联系本站删除，谢谢合作！

    音效素材网(YinXiaoSuCai.CN)

    Copyright © 1999-2022 音效素材网 版权所有   京ICP备11032199号-4  关于我们 | 广告合作 | 版权声明 | 联系我们 | 友情链接 | RSS订阅 |
    MsSqlMysqloracleMariaDBSQLiteDB2