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

原文: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的路上,仍时时不忘自己是个老SQL Server DBA。

==============================================================

蒋委员长的字,与诸君共勉!

时间: 2017-03-22

SQL Server--获取磁盘空间使用情况的相关文章

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

对于DBA来说,监控磁盘使用情况是必要的工作,然后没有比较简单的方法能获取到磁盘空间使用率信息,下面总结下这些年攒下的脚本: 最常用的查看磁盘剩余空间,这个属于DBA入门必记的东西: -- 查看磁盘可用空间 EXEC master.dbo.xp_fixeddrives xp_fixeddrives方式有点是系统自带,可直接使用,缺点是不能查看磁盘总大小和不能查看SQL Server未使用到的磁盘信息 使用sys.dm_os_volume_stats函数 --===================

c#-如图,为什么DataSet从sql server获取的字符串数据带省略号?

问题描述 如图,为什么DataSet从sql server获取的字符串数据带省略号? 我要对数据内容进行判断,结果老是不符.后来断点调试,放大镜查看DataSet对象,如图,发现它有2列的值是带省略号的,这2列在sql server里的数据类型是 char(50).vchar(50).nvchar(50), 3种我都换过,但是程序运行后DataSet对象那里还是带省略号.怎么会出现这种情况呢 解决方案 是因为你数据库的字段长度比较大,虽然你只有两个字 实际上所占字符为你的字段长度,建议格式化,l

SQL Server 服务器磁盘测试之SQLIO篇

原文:SQL Server 服务器磁盘测试之SQLIO篇       数据库调优工作中,有一部分是需要排查IO问题的,例如IO的速度或者RAID级别无法响应高并发下的快速请求.最常见的就是查看磁盘每次读写的响应速度,通过性能计数器Avg.Disk sec/Read(Write)我们可以很直观的看到他们.不同的业务,繁忙程度也不同,需要什么样的IO做支撑?难道都用最高配的IO去配置硬件吗?能否通过一些经验和基线来判断当下磁盘环境是否能满足需求呢? 手上有4块 Inter SSD DC S3500

监测谁用了SQL Server的Tempdb空间

原文:监测谁用了SQL Server的Tempdb空间 转自:http://blogs.msdn.com/b/apgcdsd/archive/2011/02/11/sql-server-tempdb.aspx Tempdb 系统数据库是一个全局资源,供连接到 SQL Server 实例的所有用户使用.在现在的SQL Server里,其使用频率可能会超过用户的想象.如果Tempdb空间耗尽,许多操作将不能完成. 作为一个支持工程师,会被经常问到象"我的Tempdb为什么这么大?"&quo

Delphi实现获取磁盘空间大小的方法_Delphi

本文所述Delphi实例用以获取指定的磁盘空间容量大小,检测磁盘大小,从combox中选择磁盘代号等功能.点击"检测驱动器"容量信息的按钮,就可以在下边显示出该磁盘的总空间大小以及要用容量的大小.读者可根据需求添加对应的Button与label控件. 主要程序代码如下所示: unit Unit1; interface uses Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, StdCtr

jsp连sql server获取表中的某个值 并连接这个值的数据库

问题描述 jsp连sql server获取表中的某个值 并连接这个值的数据库 目前是连接到一个数据库A 根据A里的表判断登陆 但这个表里还有一个数据库B的连接字符串 类似这样 Server=192.27.94.10,11008;database=C_Model;user id=User;pwd=Pwd;Connect Timeout=30 怎么获取这个表内的字符串并连接到这个数据库B 解决方案 jsp连接sql server2005?(经测试连接成功)?? 接触Java或者JSP,难免会使用到数

centos中df—查看文件系统的磁盘空间占用情况

名称df语法df [-options]参数    -a 显示所有文件系统的磁盘使用情况,包括0块(block)的文件系统,如/proc文件系统. -k 以k字节为单位显示. -i 显示i节点信息,而不是磁盘块. -t 显示各指定类型的文件系统的磁盘空间使用情况. -x 列出不是某一指定类型文件系统的磁盘空间使用情况(与t选项相反). -T 显示文件系统类型.使用实例列出各文件系统的磁盘空间使用情况 df 列出各文件系统的i节点使用情况 df -ia 列出文件系统的类型 df -T -h更具目前磁

如何获取磁盘空间剩余空间

问题描述 在asp.net里如何获取磁盘空间剩余空间啊?就是想当用户上传文件时先检查硬盘空间的大小,如果没空间了就告诉用户 解决方案 解决方案二: 解决方案三:DriveInfodrive=newDriveInfo("C");ulongfreefreeBytesAvailable=(ulong)drive.AvailableFreeSpace; 解决方案四:http://www.builder.com.cn/2007/1107/616528.shtml解决方案五:DriveInfo.A

sql server 获取每一个类别中值最大的一条数据

原文:sql server 获取每一个类别中值最大的一条数据 /* 数据如下: name val memo a 2 a2(a的第二个值) a 1 a1--a的第一个值 a 3 a3:a的第三个值 b 1 b1--b的第一个值 b 3 b3:b的第三个值 b 2 b2b2b2b2 b 4 b4b4 b 5 b5b5b5b5b5 */ --创建表并插入数据: create table tb(name varchar(10),val int,memo varchar(20)) insert into