MSSQL 監控數據/日志文件增長實現方法

2024-08-31 00:45:06

字體：大中小

來源：轉載

供稿：網友

前幾天,在所有數據庫服務器部署了監控磁盤空間的存儲過程和作業后（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

接下來，我們創建存儲過程，負責來收集、統計這些數據庫的文件的相關信息。關于環比/同比,正常情況一般是:
環比: (指標當前值 - 指標值(上個月同一天))/ 指標值(上個月同一天) 。
同比: (指標當前值 - 指標值(去年月同一天))/ 指標值(去年月同一天) 。
其實如果關注每天的數據文件變化情況，這個代碼里面的環比、同比其實意義不大，其實我們可以這樣定義環比、同比：
環比：（指標當前值 - 指標值（昨天）)/指標值（昨天）。
同比：（指標當前值 - 指標值（上個月））/指標值（上個月）
當然，你也可以把這四個指標都加上，對比參考，側重點不同而已。

復制代碼代碼如下:

上一篇：如何控制SQLServer中的跟蹤標記

下一篇：MSSQL監控數據庫的DDL操作(創建，修改，刪除存儲過程，創建，修