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
GOSET 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