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

2020-07-25 12:40:35

字体：大中小

来源：转载

供稿：网友

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

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

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

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

使用sys.dm_os_volume_stats函数

--======================================================================--查看数据库文件使用的磁盘空间使用情况WITH T1 AS (SELECT DISTINCTREPLACE(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_GBFROM  sys.master_files AS fCROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs)SELECTDrive_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_PercentFROM T1

查询效果：

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

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

USE [monitor]GO/****** Object: StoredProcedure [dbo].[usp_get_disk_free_size]  Script Date: 2016/5/25 18:21:11 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:    GGA-- Create date:  2016-2-1-- Description:  收集磁盘剩余空间信息-- =============================================CREATE PROCEDURE [dbo].[usp_get_disk_free_size]ASBEGIN  SET NOCOUNT ON;  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;--==========================================--创建相关表IF OBJECT_ID('server_disk_usage') IS NULLBEGIN  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 @diskEXEC xp_fixeddrives--更新当前磁盘的剩余空间信息UPDATE MSET M.[free_siez_mb]=D.[free_siez_mb]FROM [dbo].[server_disk_usage] AS MINNER JOIN @disk AS DON 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 DWHERE NOT EXISTS(  SELECT 1  FROM [dbo].[server_disk_usage] AS M   WHERE M.[disk_num]=D.[disk_num] )ENDGO/****** Object: StoredProcedure [dbo].[usp_get_disk_total_size]  Script Date: 2016/5/25 18:21:11 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:    GGA-- Create date:  2016-2-1-- Description:  收集磁盘总空间信息-- =============================================CREATE PROCEDURE [dbo].[usp_get_disk_total_size]ASBEGIN  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--==========================================--开启CMDShellEXEC 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))--============================================--将需要检查的磁盘放入临时表#checkDisksSELECT ROW_NUMBER()OVER(ORDER BY [disk_num]) AS RID,[disk_num]INTO #checkDisksFROM [dbo].[server_disk_usage] WHERE [total_size_mb] = 0;--============================================--循环临时表#checkDisks检查每个磁盘的总量DECLARE @disk_num NVARCHAR(20)DECLARE @total_size_mb INTDECLARE @sql NVARCHAR(200)DECLARE @max INTDECLARE @min INTSELECT @max=MAX(RID),@min=MIN(RID) FROM #checkDisksWHILE(@min<=@max)BEGINSELECT @disk_num=[disk_num] FROM #checkDisks WHERE RID=@minSET @sql = N'EXEC sys.xp_cmdshell ''fsutil volume diskfree '+@disk_num+':'+''''PRINT @sqlINSERT INTO #tempDisksEXEC sys.sp_executesql @sqlSELECT @total_size_mb=CAST((RIGHT(DiskSpace,LEN(DiskSpace)  -CHARINDEX(': ',DiskSpace)-1)) AS BIGINT)/1024/1024FROM #tempDisks WHERE id = 2SELECT @total_size_mb,@disk_numUPDATE [dbo].[server_disk_usage]SET [total_size_mb]=@total_size_mbWHERE [disk_num]=@disk_num--SELECT * FROM #tempDisksTRUNCATE TABLE #tempDisksSET @min=@min+1END--==========================================--CMDShellEXEC sp_configure 'xp_cmdshell',0;EXEC sp_configure 'show advanced options',1;RECONFIGURE WITH OVERRIDE;ENDGO/****** Object: StoredProcedure [dbo].[usp_get_disk_usage]  Script Date: 2016/5/25 18:21:11 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:    GGA-- Create date:  2016-2-1-- Description:  收集磁盘总空间信息-- =============================================CREATE PROCEDURE [dbo].[usp_get_disk_usage]ASBEGIN  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]ENDGO--==================================--查看磁盘空间使用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语句，如：

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

错误消息为：

配置选项 '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下干掉“用户事务”呢？

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

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中执行结果为：

消息 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开了头，当然相关信息也可以使用类似方式来获取啦！

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

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

运行效果为：

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

感言：

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

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

上一篇：SQL Server数据库定时自动备份

下一篇：SQL Server实现用触发器捕获DML操作的会话信息【实例】