SQL Server獲取磁盤(pán)空間使用情況
對(duì)于DBA來(lái)說(shuō)，監(jiān)控磁盤(pán)使用情況是必要的工作，然后沒(méi)有比較簡(jiǎn)單的方法能獲取到磁盤(pán)空間使用率信息，下面總結(jié)下這些年攢下的腳本：
最常用的查看磁盤(pán)剩余空間，這個(gè)屬于DBA入門(mén)必記的東西：
-- 查看磁盤(pán)可用空間 EXEC master.dbo.xp_fixeddrives
xp_fixeddrives方式有點(diǎn)是系統(tǒng)自帶，可直接使用，缺點(diǎn)是不能查看磁盤(pán)總大小和不能查看SQL Server未使用到的磁盤(pán)信息
使用sys.dm_os_volume_stats函數(shù)
--====================================================================== --查看數(shù)據(jù)庫(kù)文件使用的磁盤(pán)空間使用情況 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即更高版本，另外無(wú)法查到數(shù)據(jù)庫(kù)文件未使用到的磁盤(pán)
為兼容低版本，可采用xp_fixeddrives+xp_cmdshell方式來(lái)獲取，我寫(xiě)了幾個(gè)存儲(chǔ)過(guò)程來(lái)獲取磁盤(pán)信息：
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: 收集磁盤(pán)剩余空間信息
-- =============================================
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ù)庫(kù)使用到的磁盤(pán)剩余空間
DECLARE @disk TABLE(
[disk_num] VARCHAR(50),
[free_siez_mb] INT)
INSERT INTO @disk
EXEC xp_fixeddrives
--更新當(dāng)前磁盤(pán)的剩余空間信息
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]
--插入新增磁盤(pán)的剩余空間信息
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: 收集磁盤(pán)總空間信息
-- =============================================
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
--==========================================
--開(kāi)啟CMDShell
EXEC sp_configure 'show advanced options',1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'xp_cmdshell',1;
RECONFIGURE WITH OVERRIDE
--========================================
--創(chuàng)建臨時(shí)表用來(lái)存放每個(gè)盤(pán)符的數(shù)據(jù)
CREATE TABLE #tempDisks
(
ID INT IDENTITY(1,1),
DiskSpace NVARCHAR(200)
)
--============================================
--將需要檢查的磁盤(pán)放入臨時(shí)表#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)臨時(shí)表#checkDisks檢查每個(gè)磁盤(pán)的總量
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: 收集磁盤(pán)總空間信息
-- =============================================
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
--==================================
--查看磁盤(pán)空間使用
EXEC [dbo].[usp_get_disk_usage]
效果顯示：
只有第一次收集磁盤(pán)信息或第一次收集新磁盤(pán)信息時(shí)，才會(huì)調(diào)用xp_cmdshell來(lái)獲取磁盤(pán)的總大小，盡量減少xp_cmdshell開(kāi)啟帶來(lái)的風(fēng)險(xiǎn)，可配合SQL Server Agent Job來(lái)使用，定期調(diào)用存儲(chǔ)過(guò)程刷新磁盤(pán)信息，監(jiān)控程序直接訪問(wèn)數(shù)據(jù)表來(lái)或許最后一次刷新時(shí)的磁盤(pán)信息。
此方式有一缺點(diǎn)是開(kāi)啟xp_cmdshell后獲取磁盤(pán)總大小期間，其他進(jìn)程可能關(guān)閉xp_cmdshell，造成存儲(chǔ)過(guò)程執(zhí)行失敗，雖然發(fā)生概率較低，但畢竟存在。
如果想跳過(guò)存儲(chǔ)過(guò)程+SQL Server Agent Job方式，直接通過(guò)程序來(lái)調(diào)用xp_cmdshell，當(dāng)程序使用“RECONFIGURE WITH OVERRIDE”來(lái)配置時(shí)，會(huì)報(bào)如下錯(cuò)誤：
CONFIG statement cannot be used inside a user transaction.DB-Lib error message 574
錯(cuò)誤類似于我們?cè)赟SMS中使用事務(wù)包裹sp_configure語(yǔ)句，如：
BEGIN TRAN EXEC sp_configure 'show advanced options',1; RECONFIGURE WITH OVERRIDE; EXEC sp_configure 'xp_cmdshell',1; RECONFIGURE WITH OVERRIDE; COMMIT
錯(cuò)誤消息為：
配置選項(xiàng) 'show advanced options' 已從 0 更改為 1。請(qǐng)運(yùn)行 RECONFIGURE 語(yǔ)句進(jìn)行安裝。 消息 574，級(jí)別 16，狀態(tài) 0，第 3 行 在用戶事務(wù)內(nèi)不能使用 CONFIG 語(yǔ)句。 配置選項(xiàng) 'xp_cmdshell' 已從 0 更改為 1。請(qǐng)運(yùn)行 RECONFIGURE 語(yǔ)句進(jìn)行安裝。 消息 574，級(jí)別 16，狀態(tài) 0，第 5 行 在用戶事務(wù)內(nèi)不能使用 CONFIG 語(yǔ)句。
難道不能通過(guò)程序調(diào)用RECONFIGURE WITH OVERRIDE語(yǔ)句？
當(dāng)然不是，google下相關(guān)錯(cuò)誤，僅發(fā)現(xiàn)下面一個(gè)相關(guān)，有興趣的可以參考下：
https://www.sqlservercentral.com/Forums/Topic1349778-146-1.aspx
粗略看了下，使用存儲(chǔ)過(guò)程套存儲(chǔ)過(guò)程的方式來(lái)繞過(guò)報(bào)錯(cuò)，本人沒(méi)有具體測(cè)試，感覺(jué)太繁瑣，于是采用簡(jiǎn)單粗暴的方式，既然報(bào)“在用戶事務(wù)內(nèi)不能使用 CONFIG 語(yǔ)句”，哪我是否可以先COMMIT下干掉“用戶事務(wù)”呢？
基于此思路，最終測(cè)試獲得下面方式：
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)
仔細(xì)的朋友發(fā)現(xiàn)我先執(zhí)行了COMMIT, 您沒(méi)看錯(cuò)，這樣的打開(kāi)方式雖然怪異但的確是一種打開(kāi)方式，在SSMS中執(zhí)行結(jié)果為：
消息 3902，級(jí)別 16，狀態(tài) 1，第 2 行 COMMIT TRANSACTION 請(qǐng)求沒(méi)有對(duì)應(yīng)的 BEGIN TRANSACTION。 配置選項(xiàng) 'show advanced options' 已從 1 更改為 1。請(qǐng)運(yùn)行 RECONFIGURE 語(yǔ)句進(jìn)行安裝。 配置選項(xiàng) 'xp_cmdshell' 已從 1 更改為 1。請(qǐng)運(yùn)行 RECONFIGURE 語(yǔ)句進(jìn)行安裝。
雖然報(bào)錯(cuò)，但是的但是，xp_cmdshell的值已經(jīng)被設(shè)置為1，即腳本執(zhí)行生效啦！
將此代碼移植到代碼中，然后通過(guò)TRY CATCH將異常捕獲并丟棄，你就可以愉快地調(diào)用xp_cmdshell啦。
使用xp_cmdshell開(kāi)了頭，當(dāng)然相關(guān)信息也可以使用類似方式來(lái)獲取啦！
比如獲取磁盤(pán)的扇區(qū)信息：
--==================================== --使用xp_cmdshell來(lái)執(zhí)行CMD命令 --獲取磁盤(pán)扇區(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 "每個(gè)"'; GO sp_configure 'xp_cmdshell',0 GO RECONFIGURE GO sp_configure 'show advanced options', 0 GO RECONFIGURE GO
運(yùn)行效果為：
當(dāng)然你可以使用fsutil fsinfo ntfsinfo D:來(lái)獲取完整信息，但是更值得您關(guān)注的就是上面這幾行。
感言：
當(dāng)了這么多年的SQL Server DBA，現(xiàn)在找份像樣的SQL SERVER DBA的工作真不容易，一方面是當(dāng)前市場(chǎng)趨勢(shì)導(dǎo)致，另一方面也是咱DBA自己“作死”造成的，看到很多同行包括我自己都還處在“刀耕火種”時(shí)代，有問(wèn)題就在界面上點(diǎn)來(lái)點(diǎn)去，給外界一種“SQL Server很容易運(yùn)維”的假象，而再看看MySQL DBA，只要你能假裝“研究下源碼”，立馬給人一種“很牛逼”的趕腳，于是乎年薪三五十萬(wàn)不再是夢(mèng)想！
以上就是本文的全部?jī)?nèi)容，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來(lái)一定的幫助，同時(shí)也希望多多支持本站！
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信