SQL SERVER 监控数据文件增长情况-白红宇

原文:

在项目前期评估数据库的增长情况，然后根据数据库数据量的增长情况来规划存储的分配其实是一件比较麻烦的事情。因为项目没有上线，用什么来评估数据库的数据增长情况呢？如果手头没有实际的数据，我们只能从表的数量以及预计一天的数据增长情况来预估数据增长量。当然这里猜测的成分较大。这个是非常不靠谱，也是不准确的。当然我们可以监控测试环境的数据库大小的增长情况来评估数据增长情况。我们可以监控数据库大小的变化来估计生产环境的数据增长情况。当然生产环境和测试环境的区别还是蛮大的。但是这样比那种瞎猜式的还是要靠谱得多。

在项目中期，我们在管理、维护数据库当中，也是需要监控数据库的增长情况的。这样有利于我们了解系统的数据变化情况，利于长期的存储规划，也能提前发现一些异常情况，及时调整数据库数据文件的增长设置。总之来说，监控数据文件的增长情况是有必要的。数据库管理、维护也是需要大数据和BI分析的吗。这个也是一个趋势。

为了监控数据库的数据文件增长情况，我写了一个存储过程用来获取数据库数据文件的一些详细信息。然后可以按天、按周、按月份这三种频率采集数据（具体可以根据需要来采集数据）存放在日表、周表、月表。需要时，即可拿来做一下分析。

基础表Maint.DataBaseSizeDtl_Day，Maint.DataBaseSizeDtl_Week，Maint.DataBaseSizeDtl_Month

USE YourSQLDba;
GO
 
IF EXISTS(SELECT 1 FROM sys.objects WHERE type='U' AND object_id=OBJECT_ID('Maint.DataBaseSizeDtl_Day'))
BEGIN
    DROP TABLE Maint.DataBaseSizeDtl_Day;
END
GO
 
 
CREATE TABLE Maint.DataBaseSizeDtl_Day
(
     DateCD                DATETIME
    ,DataBaseId            INT 
    ,FileId                INT
    ,DataBaseName        NVARCHAR(256)
    ,LogicalName        NVARCHAR(256)
    ,FileTypeDesc        NVARCHAR(120)
    ,PhysicalName        NVARCHAR(520)
    ,StateDesc            NVARCHAR(120)
    ,MaxSize            NVARCHAR(32)
    ,IsPercentGrwoth    BIT
    ,Growth                NVARCHAR(24)
    ,IsReadOnly            BIT
    ,DataBaseSize        FLOAT
    CONSTRAINT PK_DataBaseSizeDtl_Day PRIMARY KEY(DateCD, DataBaseId,FileId)
);
 
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Database Size Detail Records every day', @level0type=N'SCHEMA', @level0name=N'Maint', @level1type=N'TABLE', @level1name=N'DataBaseSizeDtl_Day';
 
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'采集数据库记录的时间',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'DateCD';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'the datebase''s identity number',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'DataBaseId';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'the datebase file''s identity number',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'FileId';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库名称',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'DataBaseName';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库逻辑名称',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'LogicalName';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件类型',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'FileTypeDesc';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库物理文件',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'PhysicalName';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库状态',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'StateDesc';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件最大值',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'MaxSize';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件是否按百分比增长',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'IsPercentGrwoth';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件是否自动增长',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'Growth';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库是否只读',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'IsReadOnly';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件大小',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Day', @level2type=N'COLUMN',@level2name=N'DataBaseSize';
 
 
 
IF EXISTS(SELECT 1 FROM sys.objects WHERE type='U' AND object_id=OBJECT_ID('Maint.DataBaseSizeDtl_Week'))
BEGIN
    DROP TABLE Maint.DataBaseSizeDtl_Week;
END
GO
 
CREATE TABLE Maint.DataBaseSizeDtl_Week
(
     DateCD                DATETIME
    ,WeekCD                INT
    ,DataBaseId            INT 
    ,FileId                INT
    ,DataBaseName        NVARCHAR(256)
    ,LogicalName        NVARCHAR(256)
    ,FileTypeDesc        NVARCHAR(120)
    ,PhysicalName        NVARCHAR(520)
    ,StateDesc            NVARCHAR(120)
    ,MaxSize            NVARCHAR(32)
    ,IsPercentGrwoth    BIT
    ,Growth                NVARCHAR(24)
    ,IsReadOnly            BIT
    ,DataBaseSize        FLOAT
    CONSTRAINT PK_DataBaseSizeDtl_Week PRIMARY KEY(WeekCD,DateCD, DataBaseId,FileId)
);
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Database Size Detail Records every week(Sunday)', @level0type=N'SCHEMA', @level0name=N'Maint', @level1type=N'TABLE', @level1name=N'DataBaseSizeDtl_Week';
 
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'采集数据库记录的时间',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'DateCD';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'第几周',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'WeekCD';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'the datebase''s identity number',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'DataBaseId';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'the datebase file''s identity number',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'FileId';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库名称',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'DataBaseName';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库逻辑名称',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'LogicalName';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件类型',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'FileTypeDesc';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库物理文件',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'PhysicalName';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库状态',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'StateDesc';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件最大值',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'MaxSize';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件是否按百分比增长',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'IsPercentGrwoth';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件是否自动增长',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'Growth';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库是否只读',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'IsReadOnly';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件大小',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Week', @level2type=N'COLUMN',@level2name=N'DataBaseSize';
 
 
 
IF EXISTS(SELECT 1 FROM sys.objects WHERE type='U' AND object_id=OBJECT_ID('Maint.DataBaseSizeDtl_Month'))
BEGIN
    DROP TABLE Maint.DataBaseSizeDtl_Month;
END
GO
 
CREATE TABLE Maint.DataBaseSizeDtl_Month
(
     DateCD                DATETIME
    ,MonthCD            INT
    ,DataBaseId            INT 
    ,FileId                INT
    ,DataBaseName        NVARCHAR(256)
    ,LogicalName        NVARCHAR(256)
    ,FileTypeDesc        NVARCHAR(120)
    ,PhysicalName        NVARCHAR(520)
    ,StateDesc            NVARCHAR(120)
    ,MaxSize            NVARCHAR(32)
    ,IsPercentGrwoth    BIT
    ,Growth                NVARCHAR(24)
    ,IsReadOnly            BIT
    ,DataBaseSize        FLOAT
    CONSTRAINT PK_DataBaseSizeDtl_Month PRIMARY KEY(MonthCD,DateCD, DataBaseId,FileId)
);
 
 
 
USE YourSQLDba;
GO
 
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Database Size Detail Records every month(the first day)', @level0type=N'SCHEMA', @level0name=N'Maint', @level1type=N'TABLE', @level1name=N'DataBaseSizeDtl_Month';
 
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'采集数据库记录的时间',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'DateCD';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'采集数据的月份',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'MonthCD';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'the datebase''s identity number',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'DataBaseId';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'the datebase file''s identity number',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'FileId';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库名称',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'DataBaseName';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库逻辑名称',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'LogicalName';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件类型',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'FileTypeDesc';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库物理文件',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'PhysicalName';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库状态',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'StateDesc';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件最大值',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'MaxSize';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件是否按百分比增长',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'IsPercentGrwoth';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件是否自动增长',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'Growth';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库是否只读',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'IsReadOnly';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库文件大小',
                                @level0type='SCHEMA', @level0name='Maint', @level1type=N'TABLE',@level1name=N'DataBaseSizeDtl_Month', @level2type=N'COLUMN',@level2name=N'DataBaseSize';

存储过程[Maint].[Usp_Monitor_Database_Size]

作业YourSQLDba_Monitor_Database_Daily_Growth

作业YourSQLDba_Monitor_Database_Week_Growth

作业YourSQLDba_Monitor_Database_Month_Grwoth