sqlserver数据库使用空间监控

来源：互联网发布：11年总决赛数据编辑：程序博客网时间：2024/06/07 00:42

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

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)goalter table [_DBA].DBO.DB_USE_REPORT  add inserttime  datetime default getdate()

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

insert into [_DBA].DBO.DB_USE_REPORTselect @@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_fileswhere 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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        maxiangqian-- Create date: 20170207-- Description:    <monitor db use>-- =============================================CREATE PROCEDURE [dbo].[check_db_use]    ASBEGIN 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' ; ENDGO

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

邮件预警的效果图如下：

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

0 0