SQL Server获取磁盘空间使用情况

对于DBA来说，监控磁盘使用情况是必要的工作，然后没有比较简单的方法能获取到磁盘空间使用率信息，下面总结下这些年攒下的脚本：

最常用的查看磁盘剩余空间，这个属于DBA入门必记的东西：

1 2	-- 查看磁盘可用空间 EXEC master.dbo.xp_fixeddrives

xp_fixeddrives方式有点是系统自带，可直接使用，缺点是不能查看磁盘总大小和不能查看SQL Server未使用到的磁盘信息

使用sys.dm_os_volume_stats函数

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

--====================================================================== --查看数据库文件使用的磁盘空间使用情况 WITH T1 AS ( SELECT DISTINCT REPLACE(vs.volume_mount_point,':\','') AS Drive_Name , CAST(vs.total_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS Total_Space_GB , CAST(vs.available_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS Free_Space_GB FROM  sys.master_files AS f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs ) SELECT Drive_Name, Total_Space_GB, Total_Space_GB-Free_Space_GB AS Used_Space_GB, Free_Space_GB, CAST(Free_Space_GB*100/Total_Space_GB AS NUMERIC(18,2)) AS Free_Space_Percent FROM T1

查询效果：

sys.dm_os_volume_stats函数很好用，能直接查询到总空间和空闲空间，可惜只支持SQL Server 2008 R2 SP1即更高版本，另外无法查到数据库文件未使用到的磁盘

为兼容低版本，可采用xp_fixeddrives+xp_cmdshell方式来获取，我写了几个存储过程来获取磁盘信息：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210

USE [monitor] GO /****** Object: StoredProcedure [dbo].[usp_get_disk_free_size]  Script Date: 2016/5/25 18:21:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:    GGA -- Create date:  2016-2-1 -- Description:  收集磁盘剩余空间信息 -- ============================================= CREATE PROCEDURE [dbo].[usp_get_disk_free_size] AS BEGIN   SET NOCOUNT ON;   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --========================================== --创建相关表 IF OBJECT_ID('server_disk_usage') IS NULL BEGIN   CREATE TABLE [dbo].[server_disk_usage](     [disk_num] [nvarchar](10) NOT NULL,     [total_size_mb] [bigint] NOT NULL CONSTRAINT [DF_server_disk_usage_total_size_mb] DEFAULT ((0)),     [free_siez_mb] [bigint] NOT NULL CONSTRAINT [DF_server_disk_usage_free_siez_mb] DEFAULT ((0)),     [disk_info] [nvarchar](400) NOT NULL CONSTRAINT [DF_server_disk_usage_disk_info] DEFAULT (''),     [check_time] [datetime] NOT NULL CONSTRAINT [DF_server_disk_usage_check_time] DEFAULT (getdate()),      CONSTRAINT [PK_server_disk_usage] PRIMARY KEY CLUSTERED     (       [disk_num] ASC     )   ) ON [PRIMARY] END --========================================== --查看所有数据库使用到的磁盘剩余空间 DECLARE @disk TABLE(     [disk_num] VARCHAR(50),     [free_siez_mb] INT) INSERT INTO @disk EXEC xp_fixeddrives --更新当前磁盘的剩余空间信息 UPDATE M SET M.[free_siez_mb]=D.[free_siez_mb] FROM [dbo].[server_disk_usage] AS M INNER JOIN @disk AS D ON M.[disk_num]=D.[disk_num] --插入新增磁盘的剩余空间信息 INSERT INTO [dbo].[server_disk_usage] (   [disk_num],   [free_siez_mb] ) SELECT [disk_num], [free_siez_mb] FROM @disk AS D WHERE NOT EXISTS(   SELECT 1   FROM [dbo].[server_disk_usage] AS M   WHERE M.[disk_num]=D.[disk_num] ) END GO /****** Object: StoredProcedure [dbo].[usp_get_disk_total_size]  Script Date: 2016/5/25 18:21:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:    GGA -- Create date:  2016-2-1 -- Description:  收集磁盘总空间信息 -- ============================================= CREATE PROCEDURE [dbo].[usp_get_disk_total_size] AS BEGIN   SET NOCOUNT ON;   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; IF NOT EXISTS(SELECT * FROM [dbo].[server_disk_usage]     WHERE [total_size_mb] = 0) BEGIN   RETURN; END --========================================== --开启CMDShell EXEC sp_configure 'show advanced options',1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'xp_cmdshell',1; RECONFIGURE WITH OVERRIDE --======================================== --创建临时表用来存放每个盘符的数据 CREATE TABLE #tempDisks (   ID INT IDENTITY(1,1),   DiskSpace NVARCHAR(200) ) --============================================ --将需要检查的磁盘放入临时表#checkDisks SELECT ROW_NUMBER()OVER(ORDER BY [disk_num]) AS RID, [disk_num] INTO #checkDisks FROM [dbo].[server_disk_usage] WHERE [total_size_mb] = 0; --============================================ --循环临时表#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+':'+'''' PRINT @sql 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 SELECT @total_size_mb,@disk_num UPDATE [dbo].[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; END GO /****** Object: StoredProcedure [dbo].[usp_get_disk_usage]  Script Date: 2016/5/25 18:21:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:    GGA -- Create date:  2016-2-1 -- Description:  收集磁盘总空间信息 -- ============================================= CREATE PROCEDURE [dbo].[usp_get_disk_usage] AS BEGIN   SET NOCOUNT ON;   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;   EXEC [dbo].[usp_get_disk_free_size]   EXEC [dbo].[usp_get_disk_total_size]   SELECT   [disk_num] AS Drive_Name   ,CAST([total_size_mb]/1024.0 AS NUMERIC(18,2)) AS Total_Space_GB   ,CAST(([total_size_mb]-[free_siez_mb])/1024.0 AS NUMERIC(18,2)) AS Used_Space_GB   ,CAST([free_siez_mb]/1024.0 AS NUMERIC(18,2)) AS Free_Space_GB   ,CAST([free_siez_mb]*100/[total_size_mb] AS NUMERIC(18,2)) AS Free_Space_Percent   ,[disk_info]   ,[check_time]   FROM [monitor].[dbo].[server_disk_usage] END GO --================================== --查看磁盘空间使用 EXEC [dbo].[usp_get_disk_usage]

效果显示：

只有第一次收集磁盘信息或第一次收集新磁盘信息时，才会调用xp_cmdshell来获取磁盘的总大小，尽量减少xp_cmdshell开启带来的风险，可配合SQL Server Agent Job来使用，定期调用存储过程刷新磁盘信息，监控程序直接访问数据表来或许最后一次刷新时的磁盘信息。

此方式有一缺点是开启xp_cmdshell后获取磁盘总大小期间，其他进程可能关闭xp_cmdshell，造成存储过程执行失败，虽然发生概率较低，但毕竟存在。

如果想跳过存储过程+SQL Server Agent Job方式，直接通过程序来调用xp_cmdshell，当程序使用“RECONFIGURE WITH OVERRIDE”来配置时，会报如下错误：

CONFIG statement cannot be used inside a user transaction.DB-Lib error message 574

错误类似于我们在SSMS中使用事务包裹sp_configure语句，如：

1 2 3 4 5 6

BEGIN TRAN EXEC sp_configure 'show advanced options',1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'xp_cmdshell',1; RECONFIGURE WITH OVERRIDE; COMMIT

错误消息为：

1 2 3 4 5 6

配置选项 'show advanced options' 已从 0 更改为 1。请运行 RECONFIGURE 语句进行安装。 消息 574，级别 16，状态 0，第 3 行 在用户事务内不能使用 CONFIG 语句。 配置选项 'xp_cmdshell' 已从 0 更改为 1。请运行 RECONFIGURE 语句进行安装。 消息 574，级别 16，状态 0，第 5 行 在用户事务内不能使用 CONFIG 语句。

难道不能通过程序调用RECONFIGURE WITH OVERRIDE语句？

当然不是，google下相关错误，仅发现下面一个相关，有兴趣的可以参考下：

https://www.sqlservercentral.com/Forums/Topic1349778-146-1.aspx

粗略看了下，使用存储过程套存储过程的方式来绕过报错，本人没有具体测试，感觉太繁琐，于是采用简单粗暴的方式，既然报“在用户事务内不能使用 CONFIG 语句”，哪我是否可以先COMMIT下干掉“用户事务”呢？

基于此思路，最终测试获得下面方式：

1 2 3 4 5 6 7 8 9

DECLARE @sql VARCHAR(2000) SET @sql =' COMMIT; EXEC sp_configure ''show advanced options'',1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure ''xp_cmdshell'',1; RECONFIGURE WITH OVERRIDE; ' EXEC(@sql)

仔细的朋友发现我先执行了COMMIT, 您没看错，这样的打开方式虽然怪异但的确是一种打开方式，在SSMS中执行结果为：

1 2 3 4

消息 3902，级别 16，状态 1，第 2 行 COMMIT TRANSACTION 请求没有对应的 BEGIN TRANSACTION。 配置选项 'show advanced options' 已从 1 更改为 1。请运行 RECONFIGURE 语句进行安装。 配置选项 'xp_cmdshell' 已从 1 更改为 1。请运行 RECONFIGURE 语句进行安装。

虽然报错，但是的但是，xp_cmdshell的值已经被设置为1，即脚本执行生效啦！

将此代码移植到代码中，然后通过TRY CATCH将异常捕获并丢弃，你就可以愉快地调用xp_cmdshell啦。

使用xp_cmdshell开了头，当然相关信息也可以使用类似方式来获取啦！

比如获取磁盘的扇区信息：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

--==================================== --使用xp_cmdshell来执行CMD命令 --获取磁盘扇区信息 EXEC sp_configure 'show advanced options',1 GO RECONFIGURE GO sp_configure 'xp_cmdshell',1 GO RECONFIGURE GO EXEC xp_cmdshell 'fsutil fsinfo ntfsinfo D: | find "每个"'; GO sp_configure 'xp_cmdshell',0 GO RECONFIGURE GO sp_configure 'show advanced options', 0 GO RECONFIGURE GO

运行效果为：

当然你可以使用fsutil fsinfo ntfsinfo D:来获取完整信息，但是更值得您关注的就是上面这几行。

感言：

当了这么多年的SQL Server DBA，现在找份像样的SQL SERVER DBA的工作真不容易，一方面是当前市场趋势导致，另一方面也是咱DBA自己“作死”造成的，看到很多同行包括我自己都还处在“刀耕火种”时代，有问题就在界面上点来点去，给外界一种“SQL Server很容易运维”的假象，而再看看MySQL DBA，只要你能假装“研究下源码”，立马给人一种“很牛逼”的赶脚，于是乎年薪三五十万不再是梦想！

以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持脚本之家！

原文链接：http://www.cnblogs.com/TeyGao/p/6602090.html

如对本文有疑问，请提交到交流社区，广大热心网友会为你解答！！ 点击进入社区