监控SQL Server的磁盘使用空间

来源：互联网发布：张麟征知乎编辑：程序博客网时间：2024/05/18 17:23

下面SQL会列出磁盘可用空间低于15%的磁盘信息：

SELECT DISTINCT vs.volume_mount_point AS Drive_Name,  vs.total_bytes/1024/1024/1024 AS Drive_Total_Space,  vs.available_bytes/1024/1024/1024 AS Drive_Free_Spaceinto #temp_disc_spaceFROM sys.master_files AS fCROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vsselect Drive_Name,(Drive_Free_Space * 100)/Drive_Total_Space as [% Free_space]into #temp_space_monitorfrom #temp_disc_spaceorder by Drive_Nameif exists(select 1 from #temp_space_monitor where [% Free_space] < 15)beginselect * from #temp_space_monitor where [% Free_space] < 15end drop table #temp_disc_space, #temp_space_monitor

另外，使用下面的SQL，可用查询各个磁盘的可用空间：

EXEC master.sys.xp_fixeddrives

if Exists (select 1 from sysobjects where name ='usp_SYSTEM_HEALTH_DRIVE_SPACE')begin DROP PROCEDURE [usp_SYSTEM_HEALTH_DRIVE_SPACE]end goCREATE  PROCEDURE [dbo].[usp_SYSTEM_HEALTH_DRIVE_SPACE]@MinMBFree int= 100, @Drive char(1) = NULLAS BEGIN /*  用法 : [usp_SYSTEM_HEALTH_DRIVE_SPACE] --按默认值查询所有        [usp_SYSTEM_HEALTH_DRIVE_SPACE] @MinMBFree =1000 , @Drive='C' --指定参数 */CREATE TABLE #tbl_xp_fixeddrives(Drive varchar(2) NOT NULL,[MB free] int NOT NULL)-- 4 - Populate #tbl_xp_fixeddrivesINSERT INTO #tbl_xp_fixeddrives(Drive, [MB free])EXEC master.sys.xp_fixeddrivesSELECT Drive,[MB free],CASE WHEN ([MB free] < @MinMBFree *2)   THEN   '黄色'  WHEN [MB free]<= @MinMBFree THEN '红色'  ELSE '绿色'  END  COLOR  FROM #tbl_xp_fixeddrivesWHERE Drive = ISNULL(@Drive,Drive)DROP TABLE #tbl_xp_fixeddrivesEND

0 0