01-SQLServer查看磁盘空间的使用率

1、在SQL2005、SQL2008、SQL2012、SQL2014、SQL2016、SQL2017、SQL2019版本上都做过测试。

2、没有存放SQLServer数据文件或日志文件的磁盘不显示。

3、具体脚本

SET NOCOUNT ON
IF @@VERSION LIKE 'Microsoft SQL Server 2012%'
OR @@VERSION LIKE 'Microsoft SQL Server 2014%' 
OR @@VERSION LIKE 'Microsoft SQL Server 2016%' 
OR @@VERSION LIKE 'Microsoft SQL Server 2017%'
OR @@VERSION LIKE 'Microsoft SQL Server 2019%' 
BEGIN 
SELECT DISTINCT 
LEFT(vs.volume_mount_point,1) as '盘符',
convert(varchar(30),CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0))+'GB ' as ' 总大小',
convert(varchar(30),CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0)) +'GB' as '可用大小',
convert(varchar(30),CAST((CAST(vs.total_bytes AS FLOAT) - CAST(vs.available_bytes AS FLOAT))/CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2))*100)+'%' as '磁盘使用率'
FROM sys.master_files AS f WITH (NOLOCK) 
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs order by LEFT(vs.volume_mount_point,1) OPTION (RECOMPILE)
END
ELSE IF @@VERSION LIKE '%2005%' OR @@VERSION LIKE '%2008%'
BEGIN 
create table #server_disk_usage (
disk_num nvarchar(10),
total_size_mb nvarchar(100) DEFAULT (''),
free_siez_mb nvarchar(100)DEFAULT ('')
)
create table #tempDisks( id int IDENTITY(1,1),DiskSpace nvarchar(100))

--1.收集磁盘剩余空间信息到临时表中
insert into #server_disk_usage(disk_num,free_siez_mb) exec xp_fixeddrives

--2.收集磁盘总空间信息
--==========================================
--开启CMDShell
EXEC sp_configure 'show advanced options',1;

RECONFIGURE WITH OVERRIDE;

EXEC sp_configure 'xp_cmdshell',1;

RECONFIGURE WITH OVERRIDE

--========================================

--将需要检查的磁盘放入临时表#checkDisks
SELECT 
ROW_NUMBER()OVER(ORDER BY [disk_num]) AS RID,
[disk_num]
INTO #checkDisks
FROM #server_disk_usage
WHERE [total_size_mb] = '';


--循环临时表#checkDisks检查每个磁盘的总量
DECLARE @disk_num NVARCHAR(20)
DECLARE @total_size_mb INT
DECLARE @sql NVARCHAR(200)
DECLARE @max INT
DECLARE @min INT
SELECT @max=MAX(RID),@min=MIN(RID) FROM #checkDisks

WHILE(@min<=@max)
BEGIN
SELECT @disk_num=disk_num 
FROM #checkDisks WHERE RID=@min

SET @sql = N'EXEC sys.xp_cmdshell ''fsutil volume diskfree '+@disk_num+':'+''''

INSERT INTO #tempDisks
EXEC sys.sp_executesql @sql

SELECT @total_size_mb=CAST((RIGHT(DiskSpace,LEN(DiskSpace)
-CHARINDEX(': ',DiskSpace)-1)) AS BIGINT)/1024/1024
FROM #tempDisks WHERE id = 2


UPDATE #server_disk_usage
SET total_size_mb=@total_size_mb
WHERE disk_num=@disk_num

--SELECT * FROM #tempDisks

TRUNCATE TABLE #tempDisks

SET @min=@min+1

END

--禁用CMDShell
EXEC sp_configure 'xp_cmdshell',0;

EXEC sp_configure 'show advanced options',1;

RECONFIGURE WITH OVERRIDE;


--3.查询最后的结果
select 
sdu.disk_num as '盘符',
convert(varchar(20),cast(convert(bigint,total_size_mb)/1024.0 as NUMERIC(18,2))) + 'GB' as '磁盘总大小',
convert(varchar(20),cast(convert(bigint,free_siez_mb)/1024.0 as NUMERIC(18,2))) + 'GB' as '磁盘可用空间',
convert(varchar(20),convert(dec(18,2),(convert(bigint,total_size_mb) - convert(bigint,free_siez_mb))*100 / convert(bigint,total_size_mb)))+'%' as '磁盘使用率'
from #server_disk_usage sdu

drop table #server_disk_usage
drop table #tempDisks
drop table #checkDisks
END

go

4、结果示例