监控-空间使用

原始脚本来自TG，自己对部分脚本做了调整，分享出来仅供参考，请勿整篇Copy！

使用以下语句获取【磁盘空间】

复制代码
USE [DBA_Monitor]
GO
/****** 对象:  StoredProcedure [dbo].[DBA_Pro_Get_DiskInfo]    脚本日期: 02/06/2017 17:28:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
【概要说明】
    <作    者>    DBA
    <创建时间>    2016/11/15 14:27
    <中文名称>    采集磁盘空间
    <功能说明>    采集当前SQL实例所在服务器的磁盘空间信息，以及可以备份的天数，数据库可以增长的次数等信息
    <调用方式>    作业方式定时调用为主,15分钟调用一次；错误处理后，维护人员手工调用为辅
    <执行说明>    先决条件：正确的传递服务器编号参数@ServerID和@InstanceID
            　    注意事项：获取磁盘信息，备份必须按照统一的备份表结构，即msdb.dbo.backupinfo，否则会运行失败或者统计结果错误
            　                1、磁盘分区大小没有采集到。
            　                2、磁盘分区可用率小于10%
            　                3、数据库增长一次所需的空间+数据库当天剩余时间备份所需的空间 >= 磁盘剩余可用空间
            　                4、剩余磁盘空间可以用于数据库备份的次数小于1
            　                5、剩余磁盘空间可以用于数据库增长的次数小于1
            　                6、C盘空间小于10G。
            　                以上条件的前提是按照单个磁盘分区来计算的；满足以上任何一个条件都会发送邮件

    <调用示例>  EXEC DBA_Pro_Get_DiskInfo 1002,100233,'UestMail@163.com'

【修订记录】
-------------------------------------------------------------------
    <2016/11/15 14:27>  <DBA>: 创建 
    <2016/11/15 17:44>  <DBA>: 各逻辑磁盘总大小划定后基本不会变动，调整为获取硬件信息时一并获取
******************************************************************/
ALTER PROCEDURE [dbo].[DBA_Pro_Get_DiskInfo] 
    @ServerID        INT,
    @InstanceID        INT,
    @Recipients        VARCHAR(200)
AS
BEGIN
    SET NOCOUNT ON;

    IF (@ServerID<1000 or @ServerID>9999)
    begin
        print '服务器编号参数错误！'
        return
    end

    IF (@InstanceID/100)<>@ServerID
    BEGIN
        print '服务器编号或者实例编号参数错误！'
        return
    END

    BEGIN TRY

    DECLARE  @DBname            varchar(32)        --数据库名称
            ,@cmd                VARCHAR(500)    --命令
            ,@TempdbLetter        varchar(8)        --临时数据库所在盘符
            ,@Now                DATETIME        --当前时间
            ,@InstanceNum        INT                --安装实例个数

    SELECT @Now=GETDATE()

    --磁盘信息预览
    if object_id('tempdb..#diskinfo')is not null
        drop table #diskinfo
    create table #diskinfo
    (
        ID                        int identity(1,1),
        Letter                    varchar(8),
        TotalSize                INT,
        FreeDiskSpace            INT default(0),
        AvailableRate            varchar(8),
        GrowthNeedSpace            INT default(0),
        BackUpNeedSpaceToday    INT default(0),
        BackUpNeedSpaceOneDay    INT default(0),
        AllowGrowthTimes        Decimal(10,2) default(0),
        AllowBackupTimes        Decimal(10,2) default(0),
        IsTempdb                varchar(3) default('NO'),
        TotalSizeTime            datetime
    )

    insert into #diskinfo(Letter,FreeDiskSpace)
    EXEC MASTER..xp_fixeddrives

    /* 
    if OBJECT_ID('tempdb..#Info_Disk_TotalSize')is not null
        drop table #Info_Disk_TotalSize
    create table #Info_Disk_TotalSize
    (
        id int identity(1,1),
        letter varchar(8) not null,
        TotalSize int default(0),
        updatetime datetime default(getdate())
    )

    insert into #Info_Disk_TotalSize(letter)
    select letter from #diskinfo

    if exists(    select top 1 1 from #Info_Disk_TotalSize
                where letter not in (select letter from Info_Disk_TotalSize))
    begin
        insert into Info_Disk_TotalSize(letter) 
        select letter 
        from #diskinfo 
        where letter not in (select letter from Info_Disk_TotalSize)
    end

    --TotalSize当天没有更新，而且当前时间为早上五点到十点之间
    if ((exists(select top 1 1 from Info_Disk_TotalSize 
                where convert(varchar(10),updatetime,120)<>CONVERT(varchar(10),getdate(),120))
        and DATEPART(HH,GETDATE())>=5 
        and DATEPART(HH,GETDATE())<10)
        OR
        NOT EXISTS(SELECT TOP 1 1 FROM Info_Disk_TotalSize)
        OR EXISTS(SELECT TOP 1 1 FROM Info_Disk_TotalSize WHERE TotalSize=0 OR TotalSize IS NULL))
    begin
        declare  @ll varchar(8)
                ,@ii int
                ,@cc int
                ,@sqll varchar(200)
                ,@usedd    int
        select @ii=1
        select @cc=COUNT(1) from #Info_Disk_TotalSize
        select @sqll=''

        if object_id('tempdb..#tmp2')is not null
            drop table #tmp2
        create table #tmp2(sysinfo varchar(500))

        while @ii<=@cc
        begin
            truncate table #tmp2
            select @ll=letter from #Info_Disk_TotalSize where id=@ii

            set @usedd=0
            --::查看C盘
            --wmic LogicalDisk where "Caption='C:'" get FreeSpace,Size /value
            set @sqll='xp_cmdshell '+''''+'wmic LogicalDisk where '+'"'+'Caption='+''''''+@ll+':'+''''''+'"'+' get FreeSpace,Size /value'+''''

            INSERT INTO #tmp2
            EXEC(@sqll)

            select @usedd=CAST(REPLACE(SUBSTRING(SYSINFO,CHARINDEX('=',sysinfo)+1,len(sysinfo)-CHARINDEX('=',sysinfo)),CHAR(13),'') AS BIGINT)/(1024*1024)
            from #tmp2 
            WHERE sysinfo LIKE 'SIZE%'

            update #Info_Disk_TotalSize
            set 
                TotalSize=@usedd--,AvailableRate=CAST((FreeDiskSpace*100)/@used AS VARCHAR)+'%'
            where id=@ii and letter=@ll

            set @ii=@ii+1
        end

        update Info_Disk_TotalSize
        set 
            TotalSize=b.TotalSize,updatetime=@Now
        from Info_Disk_TotalSize a inner join #Info_Disk_TotalSize b on a.letter=b.letter
    end
    */

    update #diskinfo
    set TotalSize=b.TotalSize,TotalSizeTime=b.TotalSizeTime
    from #diskinfo a inner join Info_DiskSpace b on a.Letter=b.DriveLetter
    where b.ServerID=@ServerID

    update #diskinfo set AvailableRate=CAST((FreeDiskSpace*100)/TotalSize AS VARCHAR)+'%'

    --数据库增长信息
    if object_id('tempdb..#dbgrowthinfo')is not null
        drop table #dbgrowthinfo
    create table #dbgrowthinfo(id int identity(1,1),dbname varchar(64),type_desc varchar(10),letter varchar(8),growth decimal(8,2))

    EXEC sp_MSforeachdb 
         'insert into #dbgrowthinfo
         select "?" dbname,type_desc,left(physical_name,1)
         ,case is_percent_growth when 1 then (size*8*growth/100)*1.0/1024 else (growth*8)*1.0/1024 end
         from [?].sys.database_files d'

    --更新信息-数据库单次增长所需磁盘空间
    update #diskinfo
    set 
        GrowthNeedSpace=a.growth
    from (select letter,sum(growth)growth from #dbgrowthinfo group by letter) a
    where #diskinfo.letter=a.letter

    --更新信息-该分区数据库可以增长的次数(不考虑其他条件)
    UPDATE #diskinfo
    SET 
        AllowGrowthTimes=CASE WHEN GrowthNeedSpace>0 THEN FreeDiskSpace*1.0/GrowthNeedSpace ELSE -1 END

    --没按规划做备份，字段置为-1
    if OBJECT_ID('msdb.dbo.BackupInfo')is not null
    begin
        if OBJECT_ID('tempdb..#backTemp')is not null
            drop table #backTemp
        create table #backTemp(id int identity(1,1),rq varchar(10),letter varchar(8) not null,filesize bigint not null)

        ;with backinfo as
        (
            select CONVERT(varchar(10),B_Start,120)rq
                ,left(L_Path,1)Letter
                ,SUM(L_FileSize)size
                ,ROW_NUMBER()over(PARTITION BY left(L_Path,1) order by SUM(L_FileSize) desc)cwt
            from 
                msdb.dbo.BackupInfo
            where CONVERT(varchar(10),B_Start,120) in 
                (
                    select distinct CONVERT(varchar(10),B_Start,120)rq 
                    from msdb.dbo.BackupInfo with(nolock)
                    where B_Start>=CONVERT(varchar(10),dateadd(DAY,-30,GETDATE()),120) and FileName like '%_F_%'-- AND BackupType='D'
                )
            group by CONVERT(varchar(10),B_Start,120),left(L_Path,1)
        )
        insert into #backTemp
        select rq,Letter,size
        from 
            backinfo
        where cwt<=2

        if OBJECT_ID('tempdb..#DiskRate')is not null
            drop table #DiskRate
        create table #DiskRate(id int identity(1,1) not null,letter varchar(8) not null,rate decimal(8,6),size bigint)

        insert into #DiskRate(letter)
        select distinct letter from #backTemp

        declare  @di int
                ,@dc int
                ,@dl varchar(8)
                ,@dr decimal(8,6)
                ,@ds bigint
        select @di=1,@dc=COUNT(1) from #DiskRate

        while @di<=@dc
        begin
            select @dl= letter from #DiskRate where id=@di
            select @dr=
                        (select MAX(filesize) from #backTemp where letter=@dl)*1.0/(select MIN(filesize) from #backTemp where letter=@dl)
            
            select @ds=    CAST((select MAX(filesize) from #backTemp where letter=@dl)*@dr AS BIGINT)
            
            UPDATE #DiskRate SET rate=@dr,size=@ds WHERE id=@di
            
            set @di=@di+1
        end

        --今天备份还需要的空间
        ;with leno as
        (
            select letter,size[L_FileSize] from #DiskRate
            union all
            select left(L_Path,1)Letter,sum(-L_FileSize)L_FileSize
            from msdb.dbo.BackupInfo
            where B_Start>=CONVERT(varchar(10),dateadd(dd,-0,getdate()),120)
            group by left(L_Path,1)
        )
        --更新信息-数据库当天备份还需的磁盘空间
        update #diskinfo
        set 
            BackUpNeedSpaceToday=a.growth
        from (select letter,sum(CAST(L_FileSize AS BIGINT))*1.0/(1024*1024)[growth] from leno group by letter) a
        where #diskinfo.letter=a.letter

        --更新信息-今天备份总共需要的空间
        update a
        set
            a.BackUpNeedSpaceOneDay=(b.size*1.0/(1024*1024))
        from #diskinfo a inner join #DiskRate b on a.letter=b.letter

        update #diskinfo
        SET 
            AllowBackupTimes=CASE WHEN BackUpNeedSpaceOneDay>0 THEN FreeDiskSpace*1.0/BackUpNeedSpaceOneDay ELSE -1 END
    end
    else
    begin
        update #diskinfo
        set 
            BackUpNeedSpaceToday=-1,
            BackUpNeedSpaceOneDay=-1,
            AllowBackupTimes=-1
    end

    UPDATE #diskinfo
    SET 
        IsTempdb='YES'
    WHERE letter IN (select LEFT(physical_name,1) from tempdb.sys.database_files)

    --获取本机SQL安装实例数量
    IF OBJECT_ID('tempdb..#InstanceInfo')is not null drop table #InstanceInfo
        create table #InstanceInfo (value1 varchar(20),value2 varchar(30))

    insert into #InstanceInfo
    exec xp_regenumvalues 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\'

    select @InstanceNum=count(1) from #InstanceInfo

    DELETE FROM Info_DiskSpace WHERE ServerID=@ServerID

    INSERT INTO Info_DiskSpace(ServerID,InstanceID,InstanceNum,
            DriveLetter,TotalSize,UnUsedSize,AvailableRate,IsTempdb,
            GrowthNeedSpace,BackUpNeedSpaceToday,BackUpNeedSpaceOneDay, 
            AllowGrowthTimes,AllowBackupTimes,InsertTime,TotalSizeTime)
    SELECT @ServerID,@InstanceID,@InstanceNum
          ,Letter,TotalSize,FreeDiskSpace,AvailableRate,IsTempdb
          ,GrowthNeedSpace,BackUpNeedSpaceToday,BackUpNeedSpaceOneDay
          ,AllowGrowthTimes,AllowBackupTimes,@Now,TotalSizeTime
    FROM #diskinfo
    /* 邮件暂时不发送
    DECLARE @html_tb VARCHAR(MAX)
            ,@sub varchar(100)
            ,@ip INT
    SELECT TOP 1 @ip = substring(IntranetIP,CHARINDEX('.',IntranetIP,9)+1,dataLENgth(IntranetIP)-(CHARINDEX('.',IntranetIP,9))) FROM Info_Hardware

    if @ip is not null
        set @sub='['+cast(@ip as varchar)+'][重要]磁盘空间预警'
    else
        set @sub='[NULL][重要]磁盘空间预警'

    --Info_DiskSpace
    IF EXISTS
    (
        SELECT TOP 1 1 
        FROM 
            Info_DiskSpace AS ih 
        WHERE ih.TotalSize IS NULL OR replace(ih.AvailableRate,'%','')<10 
            OR (ih.GrowthNeedSpace+ih.BackupNeedSpaceToday)>=ih.UnUsedSize
            OR (ih.AllowBackupTimes>-1 AND ih.AllowBackupTimes<1)
            OR (ih.AllowGrowthTimes>-1 AND ih.AllowGrowthTimes<1)
            or (ih.DriveLetter='C' and UnUsedSize<10000)
    )
    BEGIN
        SET @html_tb=''

        SET @html_tb =
               N'<style>td{FONT-FAMILY: 宋体, Helvetica, sans-serif; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: 宋体, Helvetica, sans-serif; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style>
               <table width = "1200px" border="0" cellspacing="0" cellpadding="0" style="BORDER-BOTTOM: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid;BORDER-TOP: black 1px solid">
               <tr><th>服务器编号</th><th>IP地址</th><th>服务器名称</th><th>实例编号</th><th>实例名称</th><th>本机安装实例数量</th><th>磁盘盘符</th><th>磁盘总大小</th><th>磁盘剩余可用空间</th><th>磁盘可用率</th>
                   <th>是否tempdb存放磁盘</th><th>数据库单次增长所需大小</th><th>当日备份还需磁盘空间</th><th>一天备份所需磁盘空间</th><th>允许备份的次数</th><th>允许增长的次数</th><th>记录时间</th>
               </tr>' + 
               CAST (( select td = ih.ServerID,'',
                              td = ISNULL(b.IntranetIP,'NULL'),'',
                              td = CAST(HOST_NAME() AS VARCHAR(32)),'',
                              td = ih.InstanceID,'',
                              td = InstanceName ,'',
                              td = InstanceNum,'',
                              td = ih.driveletter,'',
                              td = ISNULL(CAST(TotalSize AS VARCHAR),'NULL') ,'',
                              td = ISNULL(CAST(UnUsedSize AS VARCHAR),'NULL'),'',
                              td = ISNULL(AvailableRate,'NULL'),'',
                              td = IsTempdb,'',
                              td = GrowthNeedSpace ,'',
                              td = BackupNeedSpaceToday,'',
                              td = BackupNeedSpaceOneDay ,'',
                              td = AllowBackupTimes,'',
                              td = AllowGrowthTimes,'',
                              td =convert(varchar(20),ih.InsertTime,120),''
                        FROM 
                            Info_DiskSpace AS ih LEFT JOIN Info_Hardware b on ih.ServerID=b.ServerID
                        WHERE ih.TotalSize IS NULL OR replace(ih.AvailableRate,'%','')<10 
                            OR (ih.GrowthNeedSpace+ih.BackupNeedSpaceToday)>=ih.UnUsedSize
                            OR (ih.AllowBackupTimes>-1 AND ih.AllowBackupTimes<1)
                            OR (ih.AllowGrowthTimes>-1 AND ih.AllowGrowthTimes<1)
                            or (ih.DriveLetter='C' and ih.UnUsedSize<10000)
                        FOR XML PATH('tr')
                        ) AS NVARCHAR(MAX) 
                    ) +
                N'</table><br>'

        EXEC msdb.dbo.sp_send_dbmail
            @recipients = @Recipients,
            @body = @html_tb,
            @subject = @sub,
            @body_format = 'HTML' ;
    END
    */
    END TRY
    BEGIN CATCH
        insert into ExecErrorLog(InstanceID,Error_Procname,Error_Numbers,Error_Messages,Error_Serverity,Error_States,Error_Lines)
        SELECT @InstanceID,ERROR_PROCEDURE(),ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_LINE()
    END CATCH
END
View Code
复制代码

如果没有部署统一备份，备份文件占用空间无法预警。
使用以下语句获取【数据库信息】

复制代码
USE [DBA_Monitor]
GO
/****** 对象:  StoredProcedure [dbo].[DBA_Pro_Get_DatabaseInfo]    脚本日期: 02/06/2017 17:43:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
【概要说明】
    <作    者>    DBA
    <创建时间>    2014/05/20 14:27
    <中文名称>    获取数据库信息
    <功能说明>    采集当前SQL实例下面的数据库信息
    <调用方式>    作业方式定时调用为主；错误处理后，维护人员手工调用为辅
    <执行说明>    先决条件：正确的传递实例编号参数@InstanceID
            　    注意事项：暂无
    <调用示例>  exec DBA_Pro_Get_DatabaseInfo 100233
【修订记录】
-------------------------------------------------------------------
    <2014/06/20 16:08>  <DBA>: 创建
******************************************************************/
ALTER PROC [dbo].[DBA_Pro_Get_DatabaseInfo]
    @InstanceID int
AS
BEGIN
    declare @Database_Name            varchar(64)    ,    --数据库名称
            @Database_ID            INT            ,    --数据库编号
            @Database_Size            int            ,    --数据库大小
            @CreateTime                datetime    ,    --数据库创建时间
            @Collation_Name            varchar(32) ,    --数据库排序规则
            @Owner                    varchar(32) ,    --所有者
            @Compatibility_Level    int         ,    --兼容等级
            @User_Access_Desc        varchar(32) ,    --用户访问模式
            @Is_ReadOnly_Desc        varchar(16) ,    --是否只读
            @State_Desc                varchar(16) ,    --数据库状态
            @Recovery_Model_Desc    varchar(16) ,    --恢复模式
            @Is_Published            bit         ,    --是否发布数据库1，是 0，不是
            @Is_Subscribed            bit         ,    --是否订阅数据库1，是 0，不是
            @Is_Encrypted            bit         ,    --数据库是否证书加密 1，加密 0，未加密
            @Isolation_Level_Desc    varchar(32) ,    --默认事务隔离级别(是否需要这个数据)
            @RecordTime                datetime    ,    --记录时间
            @UpdateTime                datetime    ,    --更新时间
            @master                    varchar(200)    --获取系统数据库创建时间,为了简易操作,将4个系统数据库的创建时间默认为master库的创建时间

    SET NOCOUNT ON

    IF (@InstanceID<100000 or @InstanceID>999999)
    begin
        print '数据库实例编号参数错误！'
        return
    end

    BEGIN TRY

    if OBJECT_ID('tempdb..#dbinfo')is not null
        drop table #dbinfo
    create table #dbinfo
    (
        id int identity(1,1) ,
        Database_Name varchar(64),
        Database_Size varchar(13),
        Owner varchar(32) ,
        Database_ID INT,
        createtime varchar(11) ,
        status nvarchar(600),
        Compatibility_Level tinyint
    )
    insert into #dbinfo(Database_Name,Database_Size,owner,Database_ID,createtime,status,compatibility_level)
    exec sp_helpdb

    insert into Info_Database(InstanceID,DatabaseID,DatabaseName,DatabaseSize,StateDesc,LogReuseWaitDesc,CreateDate, 
                            OwnerName,[CollationName],RecoveryModelDesc,CompatibilityLevel,UserAccessDesc,ReadOnlyDesc,
                            PageVerifyOptionDesc,IsPublished,IsDistributor,IsEncrypted,IsCdcEnabled,InsertTime)
    select 
        InstanceID=@InstanceID
        ,CAST(CAST(@InstanceID AS VARCHAR(6))+RIGHT('0'+cast(a.database_id as varchar(2)),2) AS INT) [DatabaseID]
        ,a.name [DatabaseName]
        ,ISNULL(CAST(floor(REPLACE(replace(b.Database_Size,'MB',''),'','')) AS INT),0)[DatabaseSize]
        ,a.state_desc
        ,a.log_reuse_wait_desc
        ,a.create_date
        ,suser_sname(a.owner_sid) ownername
        ,a.collation_name
        ,a.recovery_model_desc
        ,a.compatibility_level
        ,a.user_access_desc
        ,case a.is_read_only when 1 then 'READ_ONLY' when 0 then 'READ_WRITE' end as read_only_desc
        ,a.page_verify_option_desc
        ,case when (a.is_published=1 or a.is_merge_published=1) then 1 else 0 end as is_published
        ,a.is_distributor
        ,null as is_encrypted--is_encrypted for >=08
        ,null as is_cdc_enabled--is_cdc_enabled for >=08
        ,GETDATE() InsertTime
    from 
        master.sys.databases a 
    left join 
        #dbinfo b on a.name=b.Database_Name

    if OBJECT_ID('tempdb..#dbinfo')is not null
        drop table #dbinfo

    END TRY
    BEGIN CATCH
        insert into ExecErrorLog(InstanceID,Error_Procname,Error_Numbers,Error_Messages,Error_Serverity,Error_States,Error_Lines)
        SELECT @InstanceID,ERROR_PROCEDURE(),ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_LINE()
    END CATCH
END
View Code
复制代码

使用以下语句获取【数据库文件信息】

复制代码
USE [DBA_Monitor]
GO
/****** 对象:  StoredProcedure [dbo].[DBA_Pro_Get_DatabaseFileInfo]    脚本日期: 02/06/2017 17:45:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
【概要说明】
    <作    者>    DBA
    <创建时间>    2016/09/02 14:27
    <中文名称>    获取数据库文件信息
    <功能说明>    采集当前SQL实例下面的数据库文件信息
    <调用方式>    作业方式定时调用为主；错误处理后，维护人员手工调用为辅
    <执行说明>    先决条件：正确的传递实例编号参数@InstanceID
            　    注意事项：暂无
    <调用示例>  exec DBA_Pro_Get_DatabaseFileInfo 100233
【修订记录】
-------------------------------------------------------------------
    <2016/09/02 14:27>  <DBA>: 创建
******************************************************************/
ALTER PROC [dbo].[DBA_Pro_Get_DatabaseFileInfo]
    @InstanceID INT
AS
BEGIN
    SET NOCOUNT ON
    
    IF (@InstanceID < 100000 OR @InstanceID > 999999)
    BEGIN
        PRINT '数据库实例编号参数错误！'
        RETURN
    END
    
    BEGIN TRY
        IF OBJECT_ID('tempdb..#databasefiles')IS NOT NULL
            DROP TABLE #databasefiles
        
        CREATE TABLE #databasefiles
        (
            DatabaseName        VARCHAR(32),
            FileId              INT,
            LogicalName         NVARCHAR(128),
            TypeDesc            NVARCHAR(60),
            FileGroupName       NVARCHAR(60),
            PhysicalName        NVARCHAR(260),
            StateDesc           NVARCHAR(60),
            SIZE                INT,
            MAXSIZE             INT,
            Growth              INT,
            IsReadOnly          BIT,
            IsSparse            BIT,
            IsPercentGrowth     BIT,
            IsNameReserved      BIT
        )
        EXEC sp_MSforeachdb 
             'insert into #databasefiles
             select "?" dbname,d.file_id,d.name,d.type_desc,f.name filegroup,d.physical_name,d.state_desc
             ,d.size,d.max_size,d.growth,d.is_read_only,d.is_sparse,d.is_percent_growth,d.is_name_reserved
             from [?].sys.database_files d
             left join [?].sys.filegroups f
             on d.data_space_id=f.data_space_id'
        
        --datafile
        IF OBJECT_ID('tempdb..#showfilestats')IS NOT NULL
            DROP TABLE #showfilestats
        
        CREATE TABLE #showfilestats
        (
            FileId           INT,
            FILEGROUP        INT,
            TotalExtents     INT,
            UsedExtents      INT,
            LogicalName      NVARCHAR(128),
            PhysicalName     NVARCHAR(260)
        )
        EXEC sp_MSforeachdb 
             'use [?] insert into #showfilestats exec(''dbcc showfilestats'')'
        
        --logfile
        IF OBJECT_ID('tempdb..#sqlperf')IS NOT NULL
            DROP TABLE #sqlperf
        
        CREATE TABLE #sqlperf
        (
            DatabaseName     VARCHAR(32) NOT NULL,
            LogSize          DECIMAL(18, 2),
            UsedPercent      DECIMAL(18, 2),
            STATUS           BIT
        )
        INSERT INTO #sqlperf
        EXEC ('dbcc sqlperf(logspace)')
        
        INSERT INTO Info_DatabaseFiles
          (
            InstanceID,
            DatabaseName,
            FileId,
            LogicalName,
            TypeDesc,
            FileGroupName,
            PhysicalName,
            [Size],
            UnUsedSize,
            [MaxSize],
            Growth,
            IsPercentGrowth,
            IsReadOnly,
            IsSparse,
            IsNameReserved,
            StateDesc
          )
        SELECT @InstanceID,
               d.DatabaseName,
               d.FileId,
               d.LogicalName,
               d.TypeDesc,
               d.FileGroupName,
               d.PhysicalName,
               CAST(d.Size * 8 / 1024.0 AS DECIMAL(18, 2)) AS [Size],
               --,case when d.TypeDesc='LOG' then l.LogSize
               --else cast(f.TotalExtents*8*8/1024.0 as decimal(18,2)) end as DbccSize
               CASE 
                    WHEN d.TypeDesc = 'LOG' THEN CAST(l.LogSize*(100-l.UsedPercent)/100.0 AS DECIMAL(18, 2))
                    ELSE CAST((f.TotalExtents-f.UsedExtents)*8*8/1024.0 AS DECIMAL(18, 2))
               END  AS UnUsedSize,
               CASE d.MaxSize
                    WHEN -1 THEN -1
                    ELSE CONVERT(BIGINT, d.MaxSize) * 8 / 1024
               END  AS [MaxSize],
               CASE d.IsPercentGrowth
                    WHEN 1 THEN d.Growth
                    ELSE d.Growth * 8 / 1024
               END  AS Growth,
               d.IsPercentGrowth,
               d.IsReadOnly,
               d.IsSparse,
               d.IsNameReserved,
               d.StateDesc
        FROM   #databasefiles d
               LEFT JOIN #showfilestats f
                    ON  d.PhysicalName = f.PhysicalName
               LEFT JOIN #sqlperf l
                    ON  d.DatabaseName = l.DatabaseName
        
        IF OBJECT_ID('tempdb..#databasefiles')IS NOT NULL
            DROP TABLE #databasefiles
        
        IF OBJECT_ID('tempdb..#showfilestats')IS NOT NULL
            DROP TABLE #showfilestats
        
        IF OBJECT_ID('tempdb..#sqlperf')IS NOT NULL
            DROP TABLE #sqlperf
    END TRY
    BEGIN CATCH
        INSERT INTO ExecErrorLog(InstanceID,Error_Procname,Error_Numbers,Error_Messages,Error_Serverity,Error_States,Error_Lines)
        SELECT @InstanceID,ERROR_PROCEDURE(),ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_LINE()
    END CATCH
END
View Code
复制代码

使用以下语句获取【数据表大小信息】

复制代码
USE [DBA_Monitor]
GO
/****** 对象:  StoredProcedure [dbo].[DBA_Pro_Get_TableSpace]    脚本日期: 02/06/2017 17:46:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************
【概要说明】
    <作    者>    DBA
    <创建时间>    2015/01/24 14:27
    <中文名称>    获取业务数据库表大小信息
    <功能说明>    采集当前SQL实例下面的业务数据库表大小信息
    <调用方式>    作业方式定时调用为主；错误处理后，维护人员手工调用为辅
    <执行说明>    先决条件：正确的传递实例编号参数@InstanceID
            　    注意事项：请在需要的业务库下创建此过程
    <调用示例>  exec DBA_Pro_Get_TableSpace 100233
【修订记录】
-------------------------------------------------------------------
    <2015/01/24 14:27>  <DBA>: 创建 
******************************************************************/
ALTER proc [dbo].[DBA_Pro_Get_TableSpace]
    @InstanceID    int
AS
BEGIN
    SET NOCOUNT ON

    IF (@InstanceID<100000 or @InstanceID>999999)
    begin
        print '数据库实例编号参数错误！'
        return
    end

    BEGIN TRY
    IF OBJECT_ID('Tempdb..#TableInfo')IS NOT NULL
        DROP TABLE #TableInfo
    CREATE TABLE #TableInfo
    (
        DBName            VARCHAR(32),
        TableName        SYSNAME,
        ReservedPages    INT,
        UsedPages        INT,
        DataPages        INT,
        RowCounts        INT
    )

    --EXEC sp_MSForEachTable 'INSERT #TableInfo EXEC sp_spaceused ''?'''
    EXEC sp_MSforeachdb 
         'if "?" in("dbname1","dbname2","dbname3")
         insert into #TableInfo
         select "?" DBname,o.name as TableName
         ,sum(p.reserved_page_count) as ReservedPages
         ,sum(p.used_page_count) as UsedPages
         ,sum(
            case when (p.index_id<2) then (p.in_row_data_page_count+
            p.lob_used_page_count+p.row_overflow_used_page_count)
            else p.lob_used_page_count+p.row_overflow_used_page_count
            end 
         )as DataPages
         ,sum(
            case when (p.index_id<2) then p.row_count
            else 0
            end 
         )as RowCounts
         from [?].sys.dm_db_partition_stats p inner join [?].sys.tables o
         on p.object_id=o.object_id
         where p.row_count>10000
         group by o.name'

    INSERT INTO DBA_Monitor.dbo.Info_TableSpace(InstanceID,DBname,TableName,TableRows,Reserved,DataSize,IndexSize,Unused,InsertTime)
    SELECT @InstanceID,DBName,TableName,RowCounts
    ,ReservedPages*8 Reserved,DataPages*8 DataSize
    ,(CASE WHEN UsedPages>DataPages THEN (UsedPages-DataPages) ELSE 0 END)*8 IndexSize
    ,(CASE WHEN ReservedPages>UsedPages THEN (ReservedPages-UsedPages) ELSE 0 END)*8 Unused
    ,GETDATE() AS InsertTime 
    FROM #TableInfo
    ORDER BY DBName,ReservedPages DESC,RowCounts DESC

    IF OBJECT_ID('Tempdb..#TableInfo')IS NOT NULL
        DROP TABLE #TableInfo
    END TRY
    BEGIN CATCH
        insert into ExecErrorLog(InstanceID,Error_Procname,Error_Numbers,Error_Messages,Error_Serverity,Error_States,Error_Lines)
        SELECT @InstanceID,ERROR_PROCEDURE(),ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_LINE()
    END CATCH
END
View Code
复制代码

数据库服务器收集本地数据，然后使用SSIS汇集监控数据，目标服务器使用以下语句发送【可用空间预警】邮件

复制代码
USE [DBA_Monitor]
GO
/****** 对象:  StoredProcedure [dbo].[RPT_SpaceAlarm]    脚本日期: 02/06/2017 17:49:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-----------------------------------------------
  功    能：可用空间预警
  最新修改：2016/12/28
  调用示例: exec RPT_SpaceAlarm 'UestMail@163.com'
  -----------------------------------------------*/
ALTER Proc [dbo].[RPT_SpaceAlarm]
@Recipients    VARCHAR(200)
AS
BEGIN
    SET NOCOUNT ON
    declare @Subject varchar(64)
    set @Subject='[ALL][重要] 可用空间预警'

    select a.InstanceID,a.DatabaseName,a.DatabaseSize,a.DataFileSize,a.UnUsedDataFileSize,a.DiffDataSize
    ,b.LogicalName,b.PhysicalName,b.Growth,c.TotalSize,c.UnUsedSize
    into #SpaceAlarm
    from (
    select * From dbo.view_Database
    where DiffDataSize>100
    and UnUsedDataFileSize/DataFileSize<0.01
    ) a
    inner join 
    (select * from dbo.view_DatabaseFiles where TypeDesc='ROWS') b
    on a.InstanceID=b.InstanceID
    and a.DatabaseName=b.DatabaseName
    inner join dbo.Info_DiskSpace c
    on b.InstanceID=c.InstanceID
    and left(b.PhysicalName,1)=c.DriveLetter
    where c.UnusedSize<10000
    order by a.InstanceID,a.DatabaseName

    if exists(select top 1 1 FROM #SpaceAlarm)
    begin
        DECLARE @tableHTML  NVARCHAR(MAX);
        SET @tableHTML =
        N'<style>td{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style>
        <H2>可用空间预警</H2>
        报表时间：'+Convert(varchar(19),getdate(),121)+
        N'<table width = "100%" table border="0" cellspacing="0" cellpadding="0" style="BORDER-LEFT: black 1px solid;BORDER-TOP: black 1px solid">
        <tr><th>序号</th><th>主机名(IP)</th><th>数据库名称</th><th>数据库大小(MB)</th><th>数据文件大小(MB)</th><th>可用空间(MB)</th>
        <th>较7天前已用空间变化量(MB)</th><th>逻辑名称</th><th>路径</th><th>增长量(MB)</th><th>逻辑磁盘总大小(MB)</th><th>逻辑磁盘可用空间(MB)</th></tr>' +
        CAST ( ( SELECT td = ROW_NUMBER() OVER (ORDER BY a.InstanceID,a.DatabaseName),'',
                    td = b.HostName+'('+b.ShortIP+')','',
                    td = a.DatabaseName,'',
                    td = a.DatabaseSize,'',
                    td = a.DataFileSize,'',
                    td = a.UnUsedDataFileSize,'',
                    td = a.DiffDataSize,'',
                    td = a.LogicalName,'',
                    td = a.PhysicalName,'',
                    td = a.Growth,'',
                    td = a.TotalSize,'',
                    td = a.UnUsedSize,''
                 FROM #SpaceAlarm a
                 inner join Info_Server b
                 on left(a.InstanceID,4)=b.ServerID
                 ORDER BY a.InstanceID,a.DatabaseName
                  FOR XML PATH('tr')
        ) AS NVARCHAR(MAX) ) +
        N'</table>说明：收到此邮件表明对应数据库文件所在磁盘剩余空间不足，请及时清理空间。可查看【磁盘/数据库/数据库文件/数据表信息】邮件了解空间占用情况'
        +'<br /><br />邮件发送条件：
        <br />1、数据库可用空间/数所库总大小<1%，近7天数据增量>100MB
        <br />2、数据文件所在磁盘可用空间<10G';
        --print @tableHTML;
    end

    IF (@tableHTML is null)
        return
    SET @tableHTML=ISNULL(@tableHTML,'')

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name= 'LKProfile',
        @recipients= @Recipients,
        @subject = @Subject,
        @body = @tableHTML,
        @body_format = 'HTML';

    if object_id('tempdb..#SpaceAlarm')is not null
        drop table #SpaceAlarm
END
View Code
复制代码


此邮件也可以在获取磁盘空间情况时，结合备份文件将可用空间较低的预警出来。通过查看对应实例的【磁盘/数据库/数据库文件/数据表信息】邮件定位空间被哪些表占用。
样例显示实例18上的**StatDB库(数据库大小74310MB、可用空间65MB、7天增加空间1416MB)所在磁盘G盘剩余空间不多(G盘总大小102269MB、可用空间2398MB)，按这个增长趋势只需两周就会撑爆G盘。因此需重点检查G盘上保存的文件、**StatDB库下数据表信息。
使用以下语句发送【磁盘/数据库/数据库文件/数据表信息】邮件

复制代码
USE [DBA_Monitor]
GO
/****** 对象:  StoredProcedure [dbo].[RPT_DiskDatabaseTable]    脚本日期: 02/06/2017 17:47:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*-----------------------------------------------
  功    能：邮件数据库/数据库文件信息
  最新修改：2016/11/22
  调用示例: exec RPT_DiskDatabaseTable 100233,'UestMail@163.com'
  -----------------------------------------------*/
ALTER Proc [dbo].[RPT_DiskDatabaseTable]
@InstanceID int,
@Recipients    VARCHAR(200)
AS
BEGIN
    SET NOCOUNT ON
    declare @HostName varchar(64)
    declare @ShortIP varchar(15)
    declare @Plat varchar(32)
    declare @Subject varchar(64)
    --select @HostName=HostName from dbo.Info_Instance where InstanceID=@InstanceID
    --select @ShortIP=Reverse(left(Reverse(IntranetIP),charindex('.',Reverse(IntranetIP))-1)) FROM Info_Hardware where ServerID=left(@InstanceID,4)
    select @HostName=HostName,@ShortIP=ShortIP,@Plat=Plat From Info_Server where ServerID=left(@InstanceID,4)
    IF @ShortIP IS NOT NULL
        SET @Subject='['+@ShortIP+'][RPT] 磁盘/数据库/数据库文件/数据表信息'
    ELSE
        SET @Subject='[RPT] 磁盘/数据库/数据库文件/数据表信息'
    select * into #DiskSpace FROM [Info_DiskSpace] WHERE InstanceID=@InstanceID    
    select * into #Database FROM [view_Database] WHERE InstanceID=@InstanceID
    select * into #DatabaseFiles FROM [view_DatabaseFiles] WHERE InstanceID=@InstanceID
    select * into #TableSpace FROM [view_TableSpace] WHERE InstanceID=@InstanceID

    if exists(select top 1 1 FROM #DiskSpace)
    begin
        DECLARE @tableHTML  NVARCHAR(MAX);
        SET @tableHTML =
        N'<style>td{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style>
        <H2>【'+@Plat+'】磁盘信息</H2>
        报表时间：'+Convert(varchar(19),getdate(),121)+
        N'<table width = "100%" table border="0" cellspacing="0" cellpadding="0" style="BORDER-LEFT: black 1px solid;BORDER-TOP: black 1px solid">
        <tr><th>序号</th><th>主机名(IP)</th><th>实例个数</th><th>实例名称</th><th>逻辑盘符</th><th>总大小(MB)</th><th>可用空间(MB)</th><th>可用比率</th><th>是否Tempdb所在</th><th>增长所需空间(MB)</th><th>总大小获取时间</th></tr>' +
        CAST ( ( SELECT td = ROW_NUMBER() OVER (ORDER BY a.DriveLetter),'',
                    td = @HostName+'('+@ShortIP+')','',
                    td = a.InstanceNum,'',
                    td = b.InstanceName,'',
                    td = a.DriveLetter,'',
                    td = a.TotalSize,'',
                    td = a.UnUsedSize,'',
                    td = a.AvailableRate,'',
                    td = a.IsTempdb,'',
                    td = a.GrowthNeedSpace,'',
                    td = convert(varchar(19),a.TotalSizeTime,121),''
                 FROM #DiskSpace a
                 inner join Info_Instance b
                 on a.ServerID=b.ServerID
                 ORDER BY a.DriveLetter
                  FOR XML PATH('tr')
        ) AS NVARCHAR(MAX) ) +
        N'</table>说明：总大小每周更新一次，可用空间每15分钟更新一次。增长所需空间，是指对应实例下所有数据库发生一次自增长需要的空间'
        +'<br /><br />邮件发送条件(周三)：
        <br />1、获取磁盘的基本信息(大小、可用空间等)';
        --print @tableHTML;
    end

    if exists(select top 1 1 FROM #Database)
    begin
        DECLARE @tableHTML1  NVARCHAR(MAX);
        SET @tableHTML1 =
        N'<style>td{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style>
        <H2>【'+@Plat+'】数据库信息</H2>
        报表时间：'+Convert(varchar(19),getdate(),121)+
        N'<table width = "100%" table border="0" cellspacing="0" cellpadding="0" style="BORDER-LEFT: black 1px solid;BORDER-TOP: black 1px solid">
        <tr><th>序号</th><th>数据库名称</th><th>数据库大小(MB)</th><th>数据文件大小(MB)</th><th>日志文件大小(MB)</th><th>可用空间(MB)</th><th>较7天前数据库变化量(MB)</th><th>较7天前已用空间变化量(MB)</th><th>数据库状态</th><th>日志等待类型</th><th>恢复模式</th></tr>' +
        CAST ( ( SELECT td = RankId,'',
                    td = DatabaseName,'',
                    td = DatabaseSize,'',
                    td = DataFileSize,'',
                    td = LogFileSize,'',
                    td = UnUsedDataFileSize,'',
                    td = DiffDatabaseSize,'',
                    td = DiffDataSize,'',
                    td = StateDesc,'',
                    td = LogReuseWaitDesc,'',
                    td = RecoveryModelDesc,''
                 FROM #Database
                 ORDER BY RankId
                  FOR XML PATH('tr')
        ) AS NVARCHAR(MAX) ) +
        N'</table>'
        +'<br />邮件发送条件(周三)：
        <br />1、获取数据库的基本信息(大小、可用空间等)，同7天前进行对比，得到差异数据';
        --print @tableHTML1;
    end

    if exists(select top 1 1 FROM #DatabaseFiles)
    begin
        DECLARE @tableHTML2  NVARCHAR(MAX);
        SET @tableHTML2 =
        N'<style>td{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style>
        <H2>【'+@Plat+'】数据库文件信息</H2>
        报表时间：'+Convert(varchar(19),getdate(),121)+
        N'<table width = "100%" table border="0" cellspacing="0" cellpadding="0" style="BORDER-LEFT: black 1px solid;BORDER-TOP: black 1px solid">
        <tr><th>序号</th><th>数据库名称</th><th>逻辑名称</th><th>文件类型</th><th>文件组</th><th>路径文件名</th><th>分配空间(MB)</th><th>可用空间(MB)</th><th>较7天前已用空间变化量(MB)</th><th>增长量(MB)</th><th>最大大小(MB)</th></tr>' +
        CAST ( ( SELECT td = RankId,'',
                    td = DatabaseName,'',
                    td = LogicalName,'',
                    td = TypeDesc,'',
                    td = ISNULL(FileGroupName,-1),'',
                    td = PhysicalName,'',
                    td = Size,'',
                    td = UnUsedSize,'',
                    td = DiffSize,'',
                    td = Growth,'',
                    td = MaxSize,''
                 FROM #DatabaseFiles
                 ORDER BY RankId
                  FOR XML PATH('tr')
        ) AS NVARCHAR(MAX) ) +
        N'</table>说明：文件组取值为-1表示没有文件组；最大大小(MB)为-1表示不限制文件增长'
        +'<br /><br />邮件发送条件(周三)：
        <br />1、获取数据库文件的基本信息(大小、存储位置、可用空间等)，同7天前进行对比，得到差异数据';
        --print @tableHTML2;
    end

    if exists(select top 1 1 FROM view_TableSpace)
    begin
        DECLARE @tableHTML3  NVARCHAR(MAX);
        SET @tableHTML3 =
        N'<style>td{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}th{FONT-FAMILY: Tahoma,宋体; FONT-SIZE: 12px; LINE-HEIGHT: 20px; TEXT-DECORATION: none;BORDER-BOTTOM: black 1px solid;BORDER-RIGHT: black 1px solid;}</style>
        <H2>【'+@Plat+'】数据表空间占用情况</H2>
        报表时间：'+Convert(varchar(19),getdate(),121)+
        N'<table width = "100%" table border="0" cellspacing="0" cellpadding="0" style="BORDER-LEFT: black 1px solid;BORDER-TOP: black 1px solid">
        <tr><th>序号</th><th>数据库名称</th><th>表名</th><th>记录数</th><th>申请大小(MB)</th><th>数据大小(MB)</th><th>索引大小(MB)</th><th>较7天前记录数变化量</th><th>较7天前已用空间变化量(MB)</th></tr>' +
        CAST ( ( SELECT td = ROW_NUMBER() OVER (ORDER BY DBname,ReservedMB desc,TableRows desc),'',
                    td = DBname,'',
                    td = TableName,'',
                    td = TableRows,'',
                    td = ReservedMB,'',
                    td = DataSizeMB,'',
                    td = IndexSizeMB,'',
                    td = DiffTableRows,'',
                    td = DiffSizeMB,''
                 FROM #TableSpace
                 ORDER BY DBname,ReservedMB desc,TableRows desc
                  FOR XML PATH('tr')
        ) AS NVARCHAR(MAX) ) +
        N'</table>'
        +'<br />邮件发送条件(周三)：
        <br />1、数据库大小>=10G，且表中记录数>10000，满足条件的数据表写入到[表大小信息表]
        <br />2、[表大小信息表]中申请空间>100M(各库最多只取空间占用前10的记录)，同7天前进行对比，得到差异数据';
        --print @tableHTML3;
    end

    IF (@tableHTML is null and @tableHTML1 is null and @tableHTML2 is null and @tableHTML3 is null)
        return
    SET @tableHTML=ISNULL(@tableHTML,'')+ISNULL(@tableHTML1,'')+ISNULL(@tableHTML2,'')+ISNULL(@tableHTML3,'')

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name= 'LKProfile',
        @recipients= @Recipients,
        @subject = @Subject,
        @body = @tableHTML,
        @body_format = 'HTML';
    if object_id('tempdb..#DiskSpace')is not null
        drop table #DiskSpace
    if object_id('tempdb..#Database')is not null
        drop table #Database
    if object_id('tempdb..#DatabaseFiles')is not null
        drop table #DatabaseFiles
    if object_id('tempdb..#TableSpace')is not null
        drop table #TableSpace
END
View Code
复制代码


定期发送【磁盘/数据库/数据库文件/数据表信息】邮件，监控近7天数据、空间变化量，利于设置文件增长量、历史数据归档等。
磁盘信息栏可以看到各磁盘的使用情况，数据库信息栏按数据库大小排序显示各数据库情况，数据库文件信息按数据库大小显示各数据库文件(可以查看具体盘符有哪些数据/日志文件)，数据表空间占用显示满足条件的表信息。样例提示我们查看**StatDB库，可以看到**PlayerDate表占用了整个库94%(70037MB/74310MB)的空间。

posted @   Uest  阅读(278)  评论(0)  编辑  收藏  举报
编辑推荐：
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
阅读排行：
· 周边上新：园子的第一款马克杯温暖上架
· Open-Sora 2.0 重磅开源！
· 分享 3 个 .NET 开源的文件压缩处理库，助力快速实现文件压缩解压功能！
· Ollama——大语言模型本地部署的极速利器
· DeepSeek如何颠覆传统软件测试？测试工程师会被淘汰吗？
点击右上角即可分享
微信分享提示
SQL AI 助手