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],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 ? 共同探讨

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!