sqlserver数据库使用空间监控

数据库使用空间监控，并且每周发邮件预警，下面是操作步骤：

1：建立一张表

CREATE TABLE [_DBA].DBO.DB_USE_REPORT(server_name NVARCHAR(20),    
database_name  NVARCHAR(20),    file_group  NVARCHAR(20),    logical_name  NVARCHAR(30),    physical_name  NVARCHAR(200),
    type_desc NVARCHAR(20),    used_size_Mb int,    allocated_size_mb int,    max_size_Mb int,    growth int,    is_percent_growth int)
go
alter table [_DBA].DBO.DB_USE_REPORT  add inserttime  datetime default getdate() 

2：在各个数据库每天收集一次数据

insert into [_DBA].DBO.DB_USE_REPORT
select @@SERVERNAME as server_name
      ,DB_NAME() as database_name
      ,case when data_space_id = 0 then 'LOG'
            else FILEGROUP_NAME(data_space_id) 
            end as file_group
      ,name as logical_name
      ,physical_name
      ,type_desc
      ,FILEPROPERTY(name,'SpaceUsed')/128.0 as used_size_Mb
      ,size/128.0 as allocated_size_mb 
      ,case when max_size = -1 then max_size 
            else max_size/128.0 
            end as max_size_Mb
      ,growth
      ,is_percent_growth,getdate()
 from sys.database_files
where state_desc = 'ONLINE'

可以写成一个作业定时监控，每天定时插入数据。

3：创建存储过程发邮件出来每周report一次

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[check_job_states]    Script Date: 2017/2/7 11:20:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:        maxiangqian
-- Create date: 20170207
-- Description:    <monitor db use>
-- =============================================
CREATE PROCEDURE [dbo].[check_db_use]
    
AS
BEGIN
 DECLARE @tableHTML  NVARCHAR(MAX) ;  
SET @tableHTML =  
    N'<H1>DB USED Report Last Week</H1>' +  
    N'<table border="1">' +  
    N'<tr><th>database_name</th><th>used_size_Mb</th>' +  
    N'<th>allocated_size_mb</th><th>type_desc</th><th>inserttime</th>' +  
    --N'<th>Expected Revenue</th></tr>' +  
    CAST ( ( 
    SELECT  td =database_name     ,   '',
      td =used_size_Mb ,   '',
      td =allocated_size_mb,   '',
      td =type_desc,   '',
      td =inserttime   from [_DBA].DBO.DB_USE_REPORT  where inserttime> (getdate()-7) order by [database_name],type_desc,inserttime desc
              FOR XML PATH('tr'), TYPE   
    ) AS NVARCHAR(MAX) ) +  
    N'</table>' ;  
EXEC msdb.dbo.sp_send_dbmail     @profile_name = 'sendmail',
    @recipients='729975475@qq.com;',  
    @subject = 'DB USED Report Last Week',  
    @body = @tableHTML,  
    @body_format = 'HTML' ; 
END
GO

然后创建一个定时作业直接每周report一次就好了。

邮件预警的效果图如下：

好了，就这么多了。你们不点个赞吗，关于sqlserver+MySQL的技术问题，欢迎加入QQ群：291519319   共同探讨