管理N台数据库服务器，每天手动检查磁盘空间？这个自动化方案让你彻底解放！
大家好。手头管着几十台数据库服务器，每天都要登录上去检查磁盘空间？太浪费时间了！今天分享一套磁盘空间自动监控告警方案，让服务器自己发现问题、主动发邮件通知你。
一、方案概述
通过SQL Server作业每天定时执行存储过程，自动收集磁盘容量信息，当剩余空间低于阈值时，自动发送告警邮件。
二、方案一：OLE自动化方式（信息最全）
USE master;GOIF EXISTS (SELECT 1 FROM sysobjects WHERE id = OBJECT_ID(N'sp_diskcapacity_alert1') AND OBJECTPROPERTY(id, 'IsProcedure') = 1) DROP PROCEDURE sp_diskcapacity_alert1;GOCREATE PROCEDURE sp_diskcapacity_alert1 @Threshold NUMERIC ASBEGIN SET NOCOUNT ON DECLARE @Result INT DECLARE @objectInfo INT DECLARE @DriveInfo CHAR(1) DECLARE @TotalSize VARCHAR(20) DECLARE @OutDrive INT DECLARE @UnitMB BIGINT = 1048576 DECLARE @HtmlContent NVARCHAR(MAX) DECLARE @FreeRat NUMERIC DECLARE @EmailHead VARCHAR(120) CREATE TABLE #DiskCapacity (DiskCD CHAR(1), FreeSize INT, TotalSize INT) INSERT #DiskCapacity (DiskCD, FreeSize) EXEC master.dbo.xp_fixeddrives EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE EXEC sp_configure 'Ole Automation Procedures', 1 RECONFIGURE WITH OVERRIDE EXEC @Result = master.sys.sp_OACreate 'Scripting.FileSystemObject', @objectInfo OUT DECLARE CR_DiskInfo CURSOR LOCAL FAST_FORWARD FOR SELECT DiskCD FROM #DiskCapacity ORDER BY DiskCD OPEN CR_DiskInfo FETCH NEXT FROM CR_DiskInfo INTO @DriveInfo WHILE @@FETCH_STATUS = 0 BEGIN EXEC @Result = sp_OAMethod @objectInfo, 'GetDrive', @OutDrive OUT, @DriveInfo EXEC @Result = sp_OAGetProperty @OutDrive, 'TotalSize', @TotalSize OUT UPDATE #DiskCapacity SET TotalSize = @TotalSize / @UnitMB WHERE DiskCD = @DriveInfo FETCH NEXT FROM CR_DiskInfo INTO @DriveInfo END CLOSE CR_DiskInfo DEALLOCATE CR_DiskInfo EXEC @Result = sp_OADestroy @objectInfo EXEC sp_configure 'Ole Automation Procedures', 0 RECONFIGURE WITH OVERRIDE EXEC sp_configure 'show advanced options', 0 RECONFIGURE WITH OVERRIDE SELECT @FreeRat = FreeRate FROM ( SELECT ROW_NUMBER() OVER (ORDER BY FreeSize / (TotalSize * 1.0) ASC) AS RowIndex, CAST((FreeSize / (TotalSize * 1.0)) * 100.0 AS INT) AS FreeRate FROM #DiskCapacity ) T WHERE RowIndex = 1 IF @FreeRat <= @Threshold BEGIN IF @FreeRat > 10 AND @FreeRat <= 20 SET @EmailHead = '数据库磁盘容量告警(告警级别3)' ELSE IF @FreeRat >= 5 AND @FreeRat <= 10 SET @EmailHead = '数据库磁盘容量告警(告警级别4)' ELSE SET @EmailHead = '数据库磁盘容量告警(告警级别5)' SET @HtmlContent = N'' + N'' + @EmailHead + '' + N'' + N'' + CAST((SELECT td = DiskCD, '', td = STR(TotalSize*1.0/1024,6,2), '', td = STR((TotalSize - FreeSize)*1.0/1024,6,2), '', td = STR(FreeSize*1.0/1024,6,2), '', td = STR((TotalSize - FreeSize)*1.0/TotalSize*100,6,2), '', td = STR(FreeSize*1.0/TotalSize*100,6,2), '' FROM #DiskCapacity FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'磁盘盘符总大小(GB)已用空间(GB)剩余空间(GB)已用比例(%)剩余比例(%)' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DataBase_DDL_Event', @recipients = 'dba@yourcompany.com', @subject = '服务器磁盘空间告警', @body = @HtmlContent, @body_format = 'HTML' END DROP TABLE #DiskCapacityENDGOEXEC sp_diskcapacity_alert1 @Threshold = 20
三、方案二：xp_cmdshell方式（功能强大）
USE [master]GOIF OBJECT_ID(N'dbo.sp_diskcapacity_alert2') IS NOT NULL DROP PROCEDURE dbo.sp_diskcapacity_alert2;GOCREATE PROCEDURE sp_diskcapacity_alert2 @Threshold NUMERIC ASBEGIN SET NOCOUNT ON DECLARE @HtmlContent NVARCHAR(MAX) DECLARE @FreeRat NUMERIC DECLARE @EmailHead VARCHAR(200) CREATE TABLE #DiskCapacity (DiskCD CHAR(4), FreeSize INT, TotalSize BIGINT) INSERT INTO #DiskCapacity (DiskCD, FreeSize) EXEC master..xp_fixeddrives EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE EXEC sp_configure 'show advanced options', 0 RECONFIGURE CREATE TABLE #DriveInfo1(ID INT IDENTITY(1,1), DiskCD VARCHAR(12)) CREATE TABLE #DriveInfo2(ID INT IDENTITY(1,1), TotalSize VARCHAR(22)) INSERT INTO #DriveInfo1(DiskCD) EXEC xp_cmdshell 'wmic LOGICALDISK get name' INSERT INTO #DriveInfo2(TotalSize) EXEC xp_cmdshell 'wmic LOGICALDISK get size' DELETE FROM #DriveInfo1 WHERE ID=1 DELETE FROM #DriveInfo2 WHERE ID=1 UPDATE #DiskCapacity SET TotalSize = ( SELECT CAST(LEFT(N.TotalSize, LEN(N.TotalSize)-1) AS BIGINT)/1024/1024 FROM #DriveInfo1 M INNER JOIN #DriveInfo2 N ON M.ID = N.ID WHERE M.DiskCD IS NOT NULL AND LEN(M.DiskCD) > 1 AND #DiskCapacity.DiskCD = LEFT(M.DiskCD, LEN(M.DiskCD)-1) ) EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'xp_cmdshell', 0 RECONFIGURE EXEC sp_configure 'show advanced options', 0 RECONFIGURE SELECT @FreeRat = FreeRate FROM ( SELECT ROW_NUMBER() OVER (ORDER BY FreeSize / (TotalSize * 1.0) ASC) AS RowIndex, CAST((FreeSize / (TotalSize * 1.0)) * 100.0 AS INT) AS FreeRate FROM #DiskCapacity ) T WHERE RowIndex = 1 IF @FreeRat <= @Threshold BEGIN SET @HtmlContent = N'...' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DataBase_DDL_Event', @recipients = 'dba@yourcompany.com', @subject = '服务器磁盘空间告警', @body = @HtmlContent, @body_format = 'HTML' END DROP TABLE #DiskCapacity, #DriveInfo1, #DriveInfo2ENDGO
四、方案三：简单告警方式（最安全）
只能获取剩余空间，适合按剩余容量阈值告警的场景：
USE [master]GOIF OBJECT_ID(N'dbo.sp_diskcapacity_alert3') IS NOT NULL DROP PROCEDURE dbo.sp_diskcapacity_alert3;GOCREATE PROCEDURE sp_diskcapacity_alert3 @DiskCapacity FLOAT ASBEGIN DECLARE @FreeSize INT DECLARE @EmailHead VARCHAR(200) DECLARE @HtmlContent NVARCHAR(MAX) CREATE TABLE #DiskCapacity (DiskCD CHAR(4), FreeSize INT) INSERT INTO #DiskCapacity (DiskCD, FreeSize) EXEC master..xp_fixeddrives SELECT @FreeSize = MIN(FreeSize) FROM #DiskCapacity IF @FreeSize / 1024.0 <= @DiskCapacity BEGIN IF @FreeSize / 1024.0 > 1 AND @FreeSize / 1024.0 <= 2 SET @EmailHead = '数据库磁盘容量告警(告警级别3)' ELSE IF @FreeSize / 1024.0 >= 0.5 AND @FreeSize / 1024.0 <= 1 SET @EmailHead = '数据库磁盘容量告警(告警级别4)' ELSE SET @EmailHead = '数据库磁盘容量告警(告警级别5)' SET @HtmlContent = N'' + N'' + @EmailHead + '' + N'' + CAST((SELECT td = DiskCD, '', td = STR(FreeSize * 1.0 / 1024, 6, 2), '' FROM #DiskCapacity FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'磁盘盘符剩余空间(GB)' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DataBase_DDL_Event', @recipients = 'dba@yourcompany.com', @subject = '服务器磁盘空间告警', @body = @HtmlContent, @body_format = 'HTML' END DROP TABLE #DiskCapacityENDGOEXEC sp_diskcapacity_alert3 @DiskCapacity = 5
五、配置数据库邮件
在使用上述存储过程前，需要先配置数据库邮件：
EXEC sp_configure 'Database Mail XPs', 1RECONFIGUREEXEC msdb.dbo.sp_send_dbmail @profile_name = 'DataBase_DDL_Event', @recipients = 'test@yourcompany.com', @subject = '测试邮件', @body = '磁盘监控已配置成功'
六、创建SQL Agent作业（定时执行）
USE msdbGOEXEC dbo.sp_add_job @job_name = N'磁盘空间监控告警'EXEC dbo.sp_add_jobstep @job_name = N'磁盘空间监控告警', @step_name = N'检查磁盘空间', @command = N'EXEC sp_diskcapacity_alert1 @Threshold = 20'EXEC dbo.sp_add_schedule @schedule_name = N'每天凌晨2点', @freq_type = 4, @freq_interval = 1, @active_start_time = 20000 EXEC dbo.sp_attach_schedule @job_name = N'磁盘空间监控告警', @schedule_name = N'每天凌晨2点'EXEC dbo.sp_add_jobserver @job_name = N'磁盘空间监控告警'
七、三种方案对比
八、安全建议
临时开启用完关闭：方案一和方案二都采用了临时开启、用完关闭的方式
内网使用：这些功能建议仅在可信内网使用
最小权限：为监控账号分配最小必要权限
九、告警邮件效果示例
收到邮件内容如下：
十、总结
一句话：三种方案任你选，配置好作业和邮件，磁盘监控全自动！
阅读原文：原文链接
该文章在 2026/4/23 16:50:29 编辑过
400 186 1886
