服务器之家

服务器之家 > 正文

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

时间:2020-05-19 13:42     来源/作者:笑东风

对于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

查询效果:

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

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]

效果显示:

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

只有第一次收集磁盘信息或第一次收集新磁盘信息时,才会调用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

运行效果为:

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

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

感言:

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

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

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

相关文章

热门资讯

歪歪漫画vip账号共享2020_yy漫画免费账号密码共享
歪歪漫画vip账号共享2020_yy漫画免费账号密码共享 2020-04-07
沙雕群名称大全2019精选 今年最火的微信群名沙雕有创意
沙雕群名称大全2019精选 今年最火的微信群名沙雕有创意 2019-07-07
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分 2019-06-21
男生常说24816是什么意思?女生说13579是什么意思?
男生常说24816是什么意思?女生说13579是什么意思? 2019-09-17
超A是什么意思 你好a表达的是什么
超A是什么意思 你好a表达的是什么 2019-06-06
返回顶部