对于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 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方式来获取，我写了几个存储过程来获取磁盘信息：
|
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 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))--============================================--将需要检查的磁盘放入临时表#checkDisksSELECTROW_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语句，如：
|
1
2
3
4
5
6
|
BEGIN TRANEXEC 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 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，只要你能假装“研究下源码”，立马给人一种“很牛逼”的赶脚，于是乎年薪三五十万不再是梦想！
以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持服务器之家！
原文链接：http://www.cnblogs.com/TeyGao/p/6602090.html
