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,只要你能假装“研究下源码”,立马给人一种“很牛逼”的赶脚,于是乎年薪三五十万不再是梦想!
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,同时也希望多多支持!
上一篇: 期末考试
下一篇: C语言程序设计练习之2013 蟠桃记