sqlserver表空间占用率(SQL Server获取磁盘空间使用情况)
sqlserver表空间占用率
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中group_concat
- mysql 操作系统时区(mysql时区查看与设置方法)
- sqlserver并发性能(sql server中的任务调度与CPU深入讲解)
- mysql删库操作记录(mysql常用sql与命令之从入门到删库跑路)
- sql怎么对一列数据分组另一行求和(分组后分组合计以及总计SQL语句稍微整理了一下)
- python连接到本地的mysql数据库(Python实现连接MySql数据库及增删改查操作详解)
- 查看mysql所有权限(MySQL 权限控制细节分析)
- mysqlselect语句汇总(MySQL Select语句是如何执行的)
- mysql拼接和过滤(mysql 如何动态修改复制过滤器)
- 如何清除mysql的binlog
- sqlserver 插入数据的触发器(SQL server 表数据改变触发发送邮件的方法)
- sqlserver分页数据重复(SQL Server 在分页获取数据的同时获取到总记录数)
- 怎样查看mysql的安装路径(MySQL中查看数据库安装路径的方法)
- mysql主从同步工作原理(MySQL是如何实现主备同步)
- mysql有哪些索引和聚簇索引(MySQL 独立索引和联合索引的选择)
- sqlserver字段增加删减(关于SQL Server中bit类型字段增删查改的一些事)
- 元宵晚会槽点多,芒果台上来就假唱,岳云鹏不说相声改评书了(元宵晚会槽点多)
- 岳云鹏跟凤凰传奇谈心,说出了人生中最重要的三个人,这才成功(岳云鹏跟凤凰传奇谈心)
- 爱情可以当饭吃吗(爱情能当饭吃吗)
- Top 3 JSHS《运动与健康科学 英文 》跻身SCI体育学期刊世界前三(Top3JSHS运动与健康科学)
- 体坛传媒LOGO全新升级,多元发展迈出坚实步伐(体坛传媒LOGO全新升级)
- 超撩人治愈的绝美水彩,原来出自她之手 一笔一画令无数人沉醉(超撩人治愈的绝美水彩)
热门推荐
- MYSQL中GROUP BY不包含所有的非聚合字段时的注意事项
- 织梦dedecms开启付费授权(去掉织梦DedeCMS列表推荐时标题被加粗的方法)
- sqlserver序列可以自定义(sql server 自定义分割月功能详解及实现代码)
- angular封装公共组件(详解Angular组件之生命周期二)
- 命令行如何使用xampp中的mysql(解决xampp自启动和mysql.sock问题)
- 护卫神主机大师教程(护卫神主机大师Linux登录账户密码忘记的解决办法)
- js里咋样设置css里面的属性(css不常见属性之pointer-events的使用方法)
- phplaravel开发规范(Laravel解决nesting level错误和隐藏index.php的问题)
- 火狐浏览器状态栏的恢复
- win7用iis搭建服务器(win7配置iis服务器图文教程)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9