• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    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,只要你能假装“研究下源码”,立马给人一种“很牛逼”的赶脚,于是乎年薪三五十万不再是梦想!

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

    您可能感兴趣的文章:
    • SqlServer如何通过SQL语句获取处理器(CPU)、内存(Memory)、磁盘(Disk)以及操作系统相关信息
    • ubuntu下磁盘空间不足导致mysql无法启动的解决方法
    • Mysql存储引擎MyISAM的常见问题(表损坏、无法访问、磁盘空间不足)
    • lnmp下如何关闭Mysql日志保护磁盘空间
    • 几个缩减MySQL以节省磁盘空间的建议
    • Mysql InnoDB删除数据后释放磁盘空间的方法
    • MySQL中查询所有数据库占用磁盘空间大小和单个库中所有表的大小的sql语句
    • mssql 监控磁盘空间告警实现方法
    上一篇:SQL Server实现用触发器捕获DML操作的会话信息【实例】
    下一篇:SQL Server数据库定时自动备份
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

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