對于DBA來說，監(jiān)控磁盤使用情況是必要的工作，然后沒有比較簡單的方法能獲取到磁盤空間使用率信息，下面總結(jié)下這些年攢下的腳本：
最常用的查看磁盤剩余空間，這個屬于DBA入門必記的東西：
-- 查看磁盤可用空間
EXEC master.dbo.xp_fixeddrives
xp_fixeddrives方式有點是系統(tǒng)自帶，可直接使用，缺點是不能查看磁盤總大小和不能查看SQL Server未使用到的磁盤信息
使用sys.dm_os_volume_stats函數(shù)
--======================================================================
--查看數(shù)據(jù)庫文件使用的磁盤空間使用情況
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函數(shù)很好用，能直接查詢到總空間和空閑空間，可惜只支持SQL Server 2008 R2 SP1即更高版本，另外無法查到數(shù)據(jù)庫文件未使用到的磁盤
為兼容低版本，可采用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;
--==========================================
--創(chuàng)建相關(guān)表
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
--==========================================
--查看所有數(shù)據(jù)庫使用到的磁盤剩余空間
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
--========================================
--創(chuàng)建臨時表用來存放每個盤符的數(shù)據(jù)
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;
--============================================
--循環(huán)臨時表#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]
效果顯示：
只有第一次收集磁盤信息或第一次收集新磁盤信息時，才會調(diào)用xp_cmdshell來獲取磁盤的總大小，盡量減少xp_cmdshell開啟帶來的風險，可配合SQL Server Agent Job來使用，定期調(diào)用存儲過程刷新磁盤信息，監(jiān)控程序直接訪問數(shù)據(jù)表來或許最后一次刷新時的磁盤信息。
此方式有一缺點是開啟xp_cmdshell后獲取磁盤總大小期間，其他進程可能關(guān)閉xp_cmdshell，造成存儲過程執(zhí)行失敗，雖然發(fā)生概率較低，但畢竟存在。
如果想跳過存儲過程+SQL Server Agent Job方式，直接通過程序來調(diào)用xp_cmdshell，當程序使用“RECONFIGURE WITH OVERRIDE”來配置時，會報如下錯誤：
CONFIG statement cannot be used inside a user transaction.DB-Lib error message 574
錯誤類似于我們在SSMS中使用事務(wù)包裹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，狀態(tài) 0，第 3 行
在用戶事務(wù)內(nèi)不能使用 CONFIG 語句。
配置選項 'xp_cmdshell' 已從 0 更改為 1。請運行 RECONFIGURE 語句進行安裝。
消息 574，級別 16，狀態(tài) 0，第 5 行
在用戶事務(wù)內(nèi)不能使用 CONFIG 語句。
難道不能通過程序調(diào)用RECONFIGURE WITH OVERRIDE語句？
當然不是，google下相關(guān)錯誤，僅發(fā)現(xiàn)下面一個相關(guān)，有興趣的可以參考下：
https://www.sqlservercentral.com/Forums/Topic1349778-146-1.aspx
粗略看了下，使用存儲過程套存儲過程的方式來繞過報錯，本人沒有具體測試，感覺太繁瑣，于是采用簡單粗暴的方式，既然報“在用戶事務(wù)內(nèi)不能使用 CONFIG 語句”，哪我是否可以先COMMIT下干掉“用戶事務(wù)”呢？
基于此思路，最終測試獲得下面方式：
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ā)現(xiàn)我先執(zhí)行了COMMIT, 您沒看錯，這樣的打開方式雖然怪異但的確是一種打開方式，在SSMS中執(zhí)行結(jié)果為：
消息 3902，級別 16，狀態(tài) 1，第 2 行
COMMIT TRANSACTION 請求沒有對應(yīng)的 BEGIN TRANSACTION。
配置選項 'show advanced options' 已從 1 更改為 1。請運行 RECONFIGURE 語句進行安裝。
配置選項 'xp_cmdshell' 已從 1 更改為 1。請運行 RECONFIGURE 語句進行安裝。
雖然報錯，但是的但是，xp_cmdshell的值已經(jīng)被設(shè)置為1，即腳本執(zhí)行生效啦！
將此代碼移植到代碼中，然后通過TRY CATCH將異常捕獲并丟棄，你就可以愉快地調(diào)用xp_cmdshell啦。
使用xp_cmdshell開了頭，當然相關(guān)信息也可以使用類似方式來獲取啦！
比如獲取磁盤的扇區(qū)信息：
--====================================
--使用xp_cmdshell來執(zhí)行CMD命令
--獲取磁盤扇區(qū)信息
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:來獲取完整信息，但是更值得您關(guān)注的就是上面這幾行。
感言：
當了這么多年的SQL Server DBA，現(xiàn)在找份像樣的SQL SERVER DBA的工作真不容易，一方面是當前市場趨勢導致，另一方面也是咱DBA自己“作死”造成的，看到很多同行包括我自己都還處在“刀耕火種”時代，有問題就在界面上點來點去，給外界一種“SQL Server很容易運維”的假象，而再看看MySQL DBA，只要你能假裝“研究下源碼”，立馬給人一種“很牛逼”的趕腳，于是乎年薪三五十萬不再是夢想！
以上就是本文的全部內(nèi)容，希望本文的內(nèi)容對大家的學習或者工作能帶來一定的幫助，同時也希望多多支持腳本之家！
您可能感興趣的文章:- SqlServer如何通過SQL語句獲取處理器(CPU)、內(nèi)存（Memory）、磁盤（Disk）以及操作系統(tǒng)相關(guān)信息
- ubuntu下磁盤空間不足導致mysql無法啟動的解決方法
- Mysql存儲引擎MyISAM的常見問題（表損壞、無法訪問、磁盤空間不足）
- lnmp下如何關(guān)閉Mysql日志保護磁盤空間
- 幾個縮減MySQL以節(jié)省磁盤空間的建議
- Mysql InnoDB刪除數(shù)據(jù)后釋放磁盤空間的方法
- MySQL中查詢所有數(shù)據(jù)庫占用磁盤空間大小和單個庫中所有表的大小的sql語句
- mssql 監(jiān)控磁盤空間告警實現(xiàn)方法
