您现在的位置是：网站首页> 编程资料编程资料
MSSQL 监控数据/日志文件增长实现方法_MsSql_
2023-05-26
634人已围观
简介 MSSQL 监控数据/日志文件增长实现方法_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
顺便吐槽一下：
相关内容
- MSSQL监控数据库的DDL操作(创建，修改，删除存储过程，创建，修改，删除表等)_MsSql_
- sqlServer 数据库语法大全_MsSql_
- MSSQL中删除用户时数据库主体在该数据库存中拥有架构 无法删除的解决方法_MsSql_
- SQL SERVER中各类触发器的完整语法及参数说明_MsSql_
- sqlSQL数据库怎么批量为存储过程/函数授权呢？_MsSql_
- 安装MSDE2000提示为了安全起见，要求使用强 SA 密码的解决方法_MsSql_
- sqlserver 存储过程中If Else的用法实例_MsSql_
- SQLSERVER数据库备份后无法还原的解决办法_MsSql_
- SQL_Server全文索引的使用实例演示_MsSql_
- SQL_Server全文索引的用法解析_MsSql_
