對于DBA來說，監(jiān)控磁盤使用情況是必要的工作，然后沒有比較簡單的方法能獲取到磁盤空間使用率信息，下面總結下這些年攢下的腳本：

最常用的查看磁盤剩余空間，這個屬于DBA入門必記的東西：

-- 查看磁盤可用空間EXEC master.dbo.xp_fixeddrives

xp_fixeddrives方式有點是系統自帶，可直接使用，缺點是不能查看磁盤總大小和不能查看SQL Server未使用到的磁盤信息

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

使用sys.dm_os_volume_stats函數

--======================================================================--查看數據庫文件使用的磁盤空間使用情況WITH T1 AS (SELECT DISTINCTREPLACE(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_GBFROM    sys.master_files AS fCROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
)SELECTDrive_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_PercentFROM 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 ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        GGA-- Create date:    2016-2-1-- Description:    收集磁盤剩余空間信息-- =============================================CREATE PROCEDURE [dbo].[usp_get_disk_free_size]ASBEGIN
    SET NOCOUNT ON;    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;--==========================================--創(chuàng)建相關表IF OBJECT_ID('server_disk_usage') IS NULLBEGIN
    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 @diskEXEC xp_fixeddrives--更新當前磁盤的剩余空間信息UPDATE MSET M.[free_siez_mb]=D.[free_siez_mb]FROM [dbo].[server_disk_usage] AS MINNER JOIN @disk AS DON 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 DWHERE NOT EXISTS(    SELECT 1
    FROM [dbo].[server_disk_usage] AS M 
    WHERE M.[disk_num]=D.[disk_num] )ENDGO/****** Object:  StoredProcedure [dbo].[usp_get_disk_total_size]    Script Date: 2016/5/25 18:21:11 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        GGA-- Create date:    2016-2-1-- Description:    收集磁盤總空間信息-- =============================================CREATE PROCEDURE [dbo].[usp_get_disk_total_size]ASBEGIN
    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--==========================================--開啟CMDShellEXEC sp_configure 'show advanced options',1;RECONFIGURE WITH OVERRIDE;EXEC sp_configure 'xp_cmdshell',1;RECONFIGURE WITH OVERRIDE--========================================--創(chuàng)建臨時表用來存放每個盤符的數據CREATE TABLE #tempDisks
(
    ID INT IDENTITY(1,1),
    DiskSpace NVARCHAR(200)
)--============================================--將需要檢查的磁盤放入臨時表#checkDisksSELECT ROW_NUMBER()OVER(ORDER BY [disk_num]) AS RID,[disk_num]INTO #checkDisksFROM [dbo].[server_disk_usage] WHERE [total_size_mb] = 0;--============================================--循環(huán)臨時表#checkDisks檢查每個磁盤的總量DECLARE @disk_num NVARCHAR(20)DECLARE @total_size_mb INTDECLARE @sql NVARCHAR(200)DECLARE @max INTDECLARE @min INTSELECT @max=MAX(RID),@min=MIN(RID) FROM #checkDisksWHILE(@min<=@max)BEGINSELECT @disk_num=[disk_num] FROM #checkDisks WHERE RID=@minSET @sql = N'EXEC sys.xp_cmdshell ''fsutil volume diskfree '+@disk_num+':'+''''PRINT @sqlINSERT INTO #tempDisksEXEC sys.sp_executesql @sqlSELECT  @total_size_mb=CAST((RIGHT(DiskSpace,LEN(DiskSpace)    -CHARINDEX(': ',DiskSpace)-1)) AS BIGINT)/1024/1024FROM #tempDisks WHERE id = 2SELECT @total_size_mb,@disk_numUPDATE [dbo].[server_disk_usage]SET [total_size_mb]=@total_size_mbWHERE [disk_num]=@disk_num--SELECT * FROM  #tempDisksTRUNCATE TABLE #tempDisksSET @min=@min+1END--==========================================--CMDShellEXEC sp_configure 'xp_cmdshell',0;EXEC  sp_configure 'show advanced options',1;RECONFIGURE WITH OVERRIDE;ENDGO/****** Object:  StoredProcedure [dbo].[usp_get_disk_usage]    Script Date: 2016/5/25 18:21:11 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        GGA-- Create date:    2016-2-1-- Description:    收集磁盤總空間信息-- =============================================CREATE PROCEDURE [dbo].[usp_get_disk_usage]ASBEGIN
    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]ENDGO--==================================--查看磁盤空間使用EXEC [dbo].[usp_get_disk_usage]

效果顯示：

只有第一次收集磁盤信息或第一次收集新磁盤信息時，才會調用xp_cmdshell來獲取磁盤的總大小，盡量減少xp_cmdshell開啟帶來的風險，可配合SQL Server Agent Job來使用，定期調用存儲過程刷新磁盤信息，監(jiān)控程序直接訪問數據表來或許最后一次刷新時的磁盤信息。

此方式有一缺點是開啟xp_cmdshell后獲取磁盤總大小期間，其他進程可能關閉xp_cmdshell，造成存儲過程執(zhí)行失敗，雖然發(fā)生概率較低，但畢竟存在。

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

如果想跳過存儲過程+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 TRANEXEC 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，狀態(tài) 0，第 3 行
在用戶事務內不能使用 CONFIG 語句。
配置選項 'xp_cmdshell' 已從 0 更改為 1。請運行 RECONFIGURE 語句進行安裝。
消息 574，級別 16，狀態(tài) 0，第 5 行
在用戶事務內不能使用 CONFIG 語句。

難道不能通過程序調用RECONFIGURE WITH OVERRIDE語句？

當然不是，google下相關錯誤，僅發(fā)現下面一個相關，有興趣的可以參考下：

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)

仔細的朋友發(fā)現我先執(zhí)行了COMMIT, 您沒看錯，這樣的打開方式雖然怪異但的確是一種打開方式，在SSMS中執(zhí)行結果為：

消息 3902，級別 16，狀態(tài) 1，第 2 行COMMIT TRANSACTION 請求沒有對應的 BEGIN TRANSACTION。
配置選項 'show advanced options' 已從 1 更改為 1。請運行 RECONFIGURE 語句進行安裝。
配置選項 'xp_cmdshell' 已從 1 更改為 1。請運行 RECONFIGURE 語句進行安裝。

雖然報錯，但是的但是，xp_cmdshell的值已經被設置為1，即腳本執(zhí)行生效啦！

將此代碼移植到代碼中，然后通過TRY CATCH將異常捕獲并丟棄，你就可以愉快地調用xp_cmdshell啦。

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

使用xp_cmdshell開了頭，當然相關信息也可以使用類似方式來獲取啦！

比如獲取磁盤的扇區(qū)信息：

--====================================--使用xp_cmdshell來執(zhí)行CMD命令--獲取磁盤扇區(qū)信息EXEC sp_configure 'show advanced options',1 GORECONFIGUREGOsp_configure 'xp_cmdshell',1 GORECONFIGUREGOEXEC xp_cmdshell 'fsutil fsinfo ntfsinfo D: | find "每個"';GOsp_configure 'xp_cmdshell',0 GORECONFIGUREGOsp_configure 'show advanced options', 0 GORECONFIGUREGO

運行效果為：

當然你可以使用fsutil fsinfo ntfsinfo D:來獲取完整信息，但是更值得您關注的就是上面這幾行。

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

感言：

當了這么多年的SQL Server DBA，現在找份像樣的SQL SERVER DBA的工作真不容易，一方面是當前市場趨勢導致，另一方面也是咱DBA自己“作死”造成的，看到很多同行包括我自己都還處在“刀耕火種”時代，有問題就在界面上點來點去，給外界一種“SQL Server很容易運維”的假象，而再看看MySQL DBA，只要你能假裝“研究下源碼”，立馬給人一種“很牛逼”的趕腳，于是乎年薪三五十萬不再是夢想！

智能運維的口號已經吹響，在轉MySQL的路上，仍時時不忘自己是個老SQL Server DBA。

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

蔣委員長的字，與諸君共勉！

http://www.cnblogs.com/TeyGao/p/6602090.html

延伸閱讀

• LINQ TO SQL和Entity Framework 的關系 你了解多少？ 2017-07-26
• SQL Server事務的隔離級別和鎖 2017-07-21
• MyBatis-sql映射文件 2017-07-21
• SQL數據庫操作(CURD) 2017-07-13
• SQL Server AlwaysOn配置兩個節(jié)點加共享文件夾仲裁見證 2017-07-13
• SQL-結構化查詢語言(1) 2017-07-12
• SQL執(zhí)行過程中的性能負載點 2017-07-10
• SQL Server特殊用法筆記 2017-07-07
• hive集成sentry的sql使用語法 2017-07-04
• SQL 基礎知識梳理（七）- 集合運算 2017-06-27

SQL Server事務的隔離級別和鎖