你的位置:首页 > 数据库

[数据库]SQL SERVER 监控数据文件增长情况


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

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

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

clipboard

基础表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]

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOIF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'Maint.Usp_Monitor_Database_Size') AND OBJECTPROPERTY(id, 'IsProcedure') =1)  DROP PROCEDURE Maint.Usp_Monitor_Database_SizeGOCREATE PROCEDURE [Maint].[Usp_Monitor_Database_Size](  @Frequency  VARCHAR(12) ='WEEK') WITH ENCRYPTION--==================================================================================================--    ProcedureName    :      Maint.Usp_Monitor_Database_Size--    Author        :      Kerry  --    CreateDate      :      2015-12-18--    Description     :      监控、记录数据库的数据文件增长变化,方便分析系统数据增长量以及规划存储/***************************************************************************************************    Parameters       :                  参数说明****************************************************************************************************      @Frequency     :      采集频率,分为日DAY,周WEEK、月MONTH采集****************************************************************************************************  Modified Date  Modified User   Version         Modified Reason****************************************************************************************************  2015-12-18       Kerry     V01.00.00    新建该存储过程。***************************************************************************************************/--==================================================================================================ASBEGIN  IF @Frequency ='WEEK'    BEGIN      INSERT INTO Maint.DataBaseSizeDtl_Week      SELECT  GETDATE()                  AS DataCD          ,DATEPART(WEEK, GETDATE())          AS WeekCD          ,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           ,Is_Percent_Growth          ,CASE WHEN is_percent_growth = 1              THEN RTRIM(CAST(Growth AS CHAR(16))) + '%'              ELSE RTRIM(CAST(CAST(Growth*8.0/1024 AS DECIMAL(10, 4)) AS CHAR(16))) + 'M'          END                      AS Growth           ,Is_Read_Only                 AS IsReadOnly           ,CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(16, 4)) AS [Size(GB)]      FROM   sys.master_files      ORDER BY 3      END  ELSE IF @Frequency='MONTH'    BEGIN      INSERT INTO Maint.DataBaseSizeDtl_MONTH      SELECT  GETDATE()                  AS DataCD          ,DATEPART(MONTH, GETDATE())          AS WeekCD          ,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           ,Is_Percent_Growth          ,CASE WHEN is_percent_growth = 1              THEN RTRIM(CAST(Growth AS CHAR(16))) + '%'              ELSE RTRIM(CAST(CAST(Growth*8.0/1024 AS DECIMAL(10, 4)) AS CHAR(16))) + 'M'          END                      AS Growth           ,Is_Read_Only                 AS IsReadOnly           ,CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(16, 4)) AS [Size(GB)]      FROM   sys.master_files      ORDER BY 3    END  ELSE IF @Frequency='DAY'   BEGIN    INSERT INTO Maint.DataBaseSizeDtl_Day      SELECT  GETDATE()                  AS DataCD          ,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           ,Is_Percent_Growth          ,CASE WHEN is_percent_growth = 1              THEN RTRIM(CAST(Growth AS CHAR(16))) + '%'              ELSE RTRIM(CAST(CAST(Growth*8.0/1024 AS DECIMAL(10, 4)) AS CHAR(16))) + 'M'          END                      AS Growth           ,Is_Read_Only                 AS IsReadOnly           ,CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(16, 4)) AS [Size(GB)]      FROM   sys.master_files      ORDER BY 3  ENDENDGO

 
作业YourSQLDba_Monitor_Database_Daily_Growth

USE [msdb]GOIF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name='YourSQLDba_Monitor_Database_Daily_Growth')BEGINEXEC msdb.dbo.sp_delete_job @job_name=N'YourSQLDba_Monitor_Database_Daily_Growth', @delete_unused_schedule=1;ENDGOBEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA_MONITORING' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA_MONITORING'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'YourSQLDba_Monitor_Database_Daily_Growth',     @enabled=1,     @notify_level_eventlog=0,     @notify_level_email=2,     @notify_level_netsend=0,     @notify_level_page=0,     @delete_level=0,     @description=N'this job is used collected the database size growth trends every day.',     @category_name=N'DBA_MONITORING',     @owner_login_name=N'sa',     @notify_email_operator_name=N'YourSQLDba_Operator', @job_id = @jobId OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'YourSQLDba_Monitor_Database_Daily_Growth_Step_One',     @step_id=1,     @cmdexec_success_code=0,     @on_success_action=1,     @on_success_step_id=0,     @on_fail_action=2,     @on_fail_step_id=0,     @retry_attempts=0,     @retry_interval=0,     @os_run_priority=0, @subsystem=N'TSQL',     @command=N'exec YourSQLDba.[Maint].[Usp_Monitor_Database_Size] @Frequency=''DAY'';',     @database_name=N'YourSQLDba',     @flags=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'YourSQLDba_Monitor_Database_Daily_Growth_Schedule_Day',     @enabled=1,     @freq_type=4,     @freq_interval=1,     @freq_subday_type=1,     @freq_subday_interval=0,     @freq_relative_interval=0,     @freq_recurrence_factor=0,     @active_start_date=20151218,     @active_end_date=99991231,     @active_start_time=235900,     @active_end_time=235959IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback:  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:GO

作业YourSQLDba_Monitor_Database_Week_Growth

USE [msdb]GOIF EXISTS(SELECT 1 FROM msdb.dbo.sysjobs WHERE name='YourSQLDba_Monitor_Database_Week_Growth')BEGIN  EXEC msdb.dbo.sp_delete_job @job_name=N'YourSQLDba_Monitor_Database_Week_Growth', @delete_unused_schedule=1ENDGOBEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA_MONITORING' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA_MONITORING'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'YourSQLDba_Monitor_Database_Week_Growth',     @enabled=1,     @notify_level_eventlog=0,     @notify_level_email=2,     @notify_level_netsend=0,     @notify_level_page=0,     @delete_level=0,     @description=N'this job is used collected the database size growth trends weekly.',     @category_name=N'DBA_MONITORING',     @owner_login_name=N'sa',     @notify_email_operator_name=N'YourSQLDba_Operator', @job_id = @jobId OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'YourSQLDba_Monitor_Database_Week_Growth_Step_One',     @step_id=1,     @cmdexec_success_code=0,     @on_success_action=1,     @on_success_step_id=0,     @on_fail_action=2,     @on_fail_step_id=0,     @retry_attempts=0,     @retry_interval=0,     @os_run_priority=0, @subsystem=N'TSQL',     @command=N'Exec YourSQLDba.Maint.Usp_Monitor_Database_Size;',     @database_name=N'YourSQLDba',     @flags=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'YourSQLDba_Monitor_Database_Week_Growth_Schedule_Week',     @enabled=1,     @freq_type=8,     @freq_interval=1,     @freq_subday_type=1,     @freq_subday_interval=0,     @freq_relative_interval=0,     @freq_recurrence_factor=1,     @active_start_date=20151218,     @active_end_date=99991231,     @active_start_time=235900,     @active_end_time=235959IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback:  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:GO

作业YourSQLDba_Monitor_Database_Month_Grwoth

USE [msdb]GOIF EXISTS(SELECT 1 FROM msdb.dbo.sysjobs WHERE name='YourSQLDba_Monitor_Database_Month_Grwoth')BEGIN  EXEC msdb.dbo.sp_delete_job @job_name=N'YourSQLDba_Monitor_Database_Month_Grwoth', @delete_unused_schedule=1;END GOBEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBA_MONITORING' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'DBA_MONITORING'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'YourSQLDba_Monitor_Database_Month_Grwoth',     @enabled=1,     @notify_level_eventlog=0,     @notify_level_email=2,     @notify_level_netsend=0,     @notify_level_page=0,     @delete_level=0,     @description=N'this job is used collected the database size growth trends every month',     @category_name=N'DBA_MONITORING',     @owner_login_name=N'sa',     @notify_email_operator_name=N'YourSQLDba_Operator', @job_id = @jobId OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'YourSQLDba_Monitor_Database_Month_Grwoth_Step_One',     @step_id=1,     @cmdexec_success_code=0,     @on_success_action=1,     @on_success_step_id=0,     @on_fail_action=2,     @on_fail_step_id=0,     @retry_attempts=0,     @retry_interval=0,     @os_run_priority=0, @subsystem=N'TSQL',     @command=N'Exec YourSQLDba.[Maint].[Usp_Monitor_Database_Size] @Frequency=''MONTH'';',     @database_name=N'YourSQLDba',     @flags=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'YourSQLDba_Monitor_Database_Month_Grwoth_Schedule',     @enabled=1,     @freq_type=16,     @freq_interval=1,     @freq_subday_type=1,     @freq_subday_interval=0,     @freq_relative_interval=0,     @freq_recurrence_factor=1,     @active_start_date=20151218,     @active_end_date=99991231,     @active_start_time=235900,     @active_end_time=235959IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback:  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:GO