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,只要你能假装“研究下源码”,立马给人一种“很牛逼”的赶脚,于是乎年薪三五十万不再是梦想!
本篇文章到此结束,如果您有相关技术方面疑问可以联系我们技术人员远程解决,感谢大家支持本站!
我想要获取技术服务或软件
服务范围:MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式:远程服务、电话支持、现场服务,沟通指定方式服务
技术标签:数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题
本站部分文章参考或来源于网络,如有侵权请联系站长。
数据库远程运维 SQL Server获取磁盘空间使用情况
相关文章
- SQL Server中QUOTENAME函数的使用
- SQLServer 错误 41396 该排序操作超出了缓冲区限制。 存储过程执行已中止。 有关详细信息,请查阅 SQL Server 联机丛书。 故障 处理 修复 支持远程
- 通过使用正确的search arguments来提高SQL Server数据库的性能
- 浅谈SQL Server 2016里TempDb的进步
- SQL Server多表查询优化方案集锦
- 强制SQL Server执行计划使用并行提升在复杂查询语句下的性能
- 实现成功:使用SQL登录Oracle数据库(sql登录oracle)
- Android开发实战之SQL Server(安卓sqlserver)
- 使用Lua连接Microsoft SQL Server数据库(luamssql)
- 使用SQL Server管理汉字数据库:优化性能与数据交互(sqlserver汉字)
- 【SQL Server自我查询:如何轻松获取你所需要的信息】(自查询sqlserver)
- [停电后SQL Server复原:一次挣扎的实践](停电后sqlserver)
- Windows XP下使用MS SQL Server 2000(xp mssql2000)
- 妙用SQL Server 配置器实现数据库管理(sqlserver配置器)
- 使用SQL Server过程提升数据处理能力.(sqlserver过程么)
- SQL Server设定:有效把握数据库风险(sqlserver设定)
- 基于SQL Server的表别名使用方法(sqlserver表别名)
- SQL Server查询之旅:揭示查询器的魅力(sqlserver查询器)
- 地址SQL Server服务器IP地址的配置与使用(sqlserver服务器ip)
- SQL Server改变数据库表结构的方法(sqlserver改基表)
- 使用SQL Server实现唯一值保证数据一致性(sqlserver唯一值)
- 查询使用SQL Server即席查询,实现快速精准的数据搜索(sqlserver即席)
- 使用SQL SERVER中文版简化工作流程(sqlserver中文版)
- olock的使用SQL Server中Nolock的优雅使用(sqlserver 中n)
- Oracle SQL实现转置-简单快捷(oracle sql转置)
- 少错误掌握Oracle SQL,尽量减少出错的可能性(oracle sql 减)