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

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