全网整合营销服务商

电脑端+手机端+微信端=数据同步管理

免费咨询热线：400-708-3566

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

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

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

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

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

使用sys.dm_os_volume_stats函数

--======================================================================
--查看数据库文件使用的磁盘空间使用情况
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方式来获取，我写了几个存储过程来获取磁盘信息：

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语句，如：

BEGIN TRAN
EXEC 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 
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，只要你能假装“研究下源码”，立马给人一种“很牛”的赶脚，于是乎年薪三五十万不再是梦想！

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


# sql server 磁盘空间  # sql 获取磁盘空间  # 存储过程  # 磁盘空间  # 报错  # 会报  # 扇区  # 数据库文件  # 刀耕火种  # 几个  # 有一  # 是一种  # 就在  # 看了  # 你可以  # 才会  # 开了  # 很容易  # 给人  # 更高  # 有兴趣  # 真不 


相关文章： 单页制作网站有哪些,朋友给我发了一个单页网站，我应该怎么修改才能把他变成自己的呢，请求高手指点迷津？  手机怎么制作网站教程步骤,手机怎么做自己的网页链接？  如何用wdcp快速搭建高效网站？  建站之星好吗？新手能否轻松上手建站？  公众号网站制作网页,微信公众号怎么制作？  网站制作大概多少钱一个,做一个平台网站大概多少钱？  建站VPS能否同时实现高效与安全翻墙？  简易网站制作视频教程,使用记事本编写一个简单的网页html文件？  如何通过免费商城建站系统源码自定义网站主题与功能？  孙琪峥织梦建站教程如何优化数据库安全？  SAX解析器是什么，它与DOM在处理大型XML文件时有何不同？  内部网站制作流程,如何建立公司内部网站？  建站之星如何修改网站生成路径？  建站之星展会模板：智能建站与自助搭建高效解决方案  建站10G流量真的够用吗？如何应对访问高峰？  如何获取PHP WAP自助建站系统源码？  哈尔滨网站建设策划,哈尔滨电工证查询网站？  如何选择高效便捷的WAP商城建站系统？  青浦网站制作公司有哪些,苹果官网发货地是哪里？  深圳 网站制作,深圳招聘网站哪个比较好一点啊？  如何在橙子建站中快速调整背景颜色？  建站主机与服务器功能差异如何区分？  网站建设设计制作营销公司南阳,如何策划设计和建设网站？  如何快速上传自定义模板至建站之星？  专业网站建设制作报价,网页设计制作要考什么证？  制作网站哪家好,cc、.co、.cm哪个域名更适合做网站？  建站之星手机一键生成：多端自适应+小程序开发快速建站指南  武汉网站设计制作公司,武汉有哪些比较大的同城网站或论坛，就是里面都是武汉人的？  建站之星下载版如何获取与安装？  制作网站建设的公司有哪些,网站建设比较好的公司都有哪些？  如何选择长沙网站建站模板？H5响应式与品牌定制哪个更优？  如何通过老薛主机一键快速建站？  如何在香港服务器上快速搭建免备案网站？  非常酷的网站设计制作软件,酷培ai教育官方网站？  武汉外贸网站制作公司,现在武汉外贸前景怎么样啊？  简单实现Android验证码  无锡营销型网站制作公司,无锡网选车牌流程？  湖南网站制作公司,湖南上善若水科技有限公司做什么的？  如何基于云服务器快速搭建个人网站？  威客平台建站流程解析：高效搭建教程与设计优化方案  网页制作模板网站推荐,网页设计海报之类的素材哪里好？  定制建站流程步骤详解：一站式方案设计与开发指南  手机网站制作与建设方案,手机网站如何建设？  品牌网站制作公司有哪些,买正品品牌一般去哪个网站买？  建站168自助建站系统：快速模板定制与SEO优化指南  c++23 std::expected怎么用 c++优雅处理函数错误返回【详解】  ,想在网上投简历，哪几个网站比较好？  中山网站制作网页,中山新生登记系统登记流程？  平台云上自助建站如何快速打造专业网站？  建站之星价格显示格式升级，你的预算足够吗？ 

返回目录 在线咨询
上一篇：Python实现文本处理中批量文件处理的详细教程【教程】
下一篇：国外VPS建站遇到问题时，如何获得有效的技术支持？

您的项目需求

*请认真填写需求信息，我们会在24小时内与您取得联系。

©  云南转妮洋科技有限公司 版权所有   滇ICP备2024034437号 
联系电话： 400-708-3566   E-mail：915688610@qq.com
主营业务：网络营销推广、网站SEO优化、网站定制、营销型网站建设、微商城制作、三级分销系统开发
电话沟通
在线客服