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

2022-11-12 09:44:52
内容摘要
这篇文章主要为大家详细介绍了SQL Server 监控数据/日志文件增长实现方法,具有一定的参考价值,可以用来参考一下。 对此感兴趣的朋友,看看idc笔记做的技术笔记! 前几天,在所有
文章正文

这篇文章主要为大家详细介绍了SQL Server 监控数据/日志文件增长实现方法,具有一定的参考价值,可以用来参考一下。

对此感兴趣的朋友,看看idc笔记做的技术笔记!

前几天,在所有数据库服务器部署了监控磁盘空间的存储过程和作业后(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 Server 监控数据/日志文件增长实现方法的内容就先介绍到这里,更多相关文章的可以留意

代码注释

作者:喵哥笔记

IDC笔记

学的不仅是技术,更是梦想!