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

前几天,在所有数据库服务器部署了监控磁盘空间的存储过程和作业后（MS sql 监控磁盘空间告警），今天突然收到了两封告警邮件，好吧，存储规划是一方面，但是，是不是要分析一下是什么原因造成磁盘空间不足的呢？会不会是因为突然暴增的日志文件，抑或是系统业务猛增导致数据量暴增，还是历史数据累计原因....分析总得有数据来支撑吧，但是现在只有那些数据文件的当前大小信息，没有数据文件的历史增长变化信息，所以，今天就想实现这么一个功能，每天（频率可以调整）去收集一下数据文件的信息，放到一个表里面，这样方便我们分析数据文件的增长演变例程，甚至你可以将数据文件的增长幅度和业务变化关联起来分析....那么接下来就是我的设计思路和实现代码，目前只是简单实现，以后将继续优化,丰富一些功能。首先我们创建一个表DiskCapacityHistory，用来保存数据库文件的历史增长变化信息：

代码如下:

USE msdb;
GOIF 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 = '数据库标识'
,@level2name = N'DataBaseID';EXEC sys.sp_addextendedproperty @name = N'MS_Description'
,@value = '文件标识'
,@level2name = N'FileID';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
,@value = '数据库名称'
,@level2name = N'DataBaseName';
EXEC sys.sp_addextendedproperty @name = N'MS_Description'
,@value = '数据库逻辑名称'
,@level2name = N'LogicalName';
EXEC sys.sp_addextendedproperty @name = N'MS_Description'
,@value = '文件类型描述'
,@level2name = N'FileTypeDesc';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
,@value = '物理数据库文件'
,@level2name = N'PhysicalName';EXEC sys.sp_addextendedproperty @name = N'MS_Description'
,@value = '文件最大大小'
,@level2name = N'MaxSize';EXEC sys.sp_addextendedproperty @name = N'MS_Description'
,@value = '文件增长类型'
,@level2name = N'GrowthType';EXEC sys.sp_addextendedproperty @name = N'MS_Description'
,@value = '是否只读类型'
,@level2name = N'IsReadOnly';EXEC sys.sp_addextendedproperty @name = N'MS_Description'
,@value = '是否按百分比增长'
,@level2name = N'IsPercentGrowth';

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
,@value = '数据文件大小(GB)'
,@level2name = N'Size';EXEC sys.sp_addextendedproperty @name = N'MS_Description'
,@value = '文件增长环比(%)'
,@level2name = N'Growth_MOM_RAT';EXEC sys.sp_addextendedproperty @name = N'MS_Description'
,@value = '文件增长同比(%)'
,@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;
GOCREATE 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),'') 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),'') 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),
 '-','') 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,'') AS INT)

或

代码如下:

CREATE TABLE #DiskCapacityHistory
 (
 DATE_CD INT,
 DataBaseID INT,
 FileID INT,
 Growth_MOM_RAT FLOAT
 ) ; INSERTINTO #DiskCapacityHistory
 SELECT M.DATE_CD,
 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,
 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,'') 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,6)) AS IDC_MOM_RAT,5,4) ORDER BY SUBSTR(DATE_CD,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/

原文链接：https://www.f2er.com/mssql/63431.html

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

猜你在找的MsSQL相关文章