[Database Management]使用sys.database_files取得所有

2011-12-17

2168
0

使用sys.database_files搭配sp_MSforeachdb取得SQL SERVER上所有資料庫檔案空間使用量的資訊。

在上一篇提到取得單一個別資料庫檔案空間使用量的資訊，一般的情況下是會取得所有資料庫的資訊，所以在本篇要示範如何使用sys.database_files搭配sp_MSforeachdb取得SQL SERVER上所有資料庫檔案空間使用量的資訊。

第一個部分我們使用WHERE 1=2的技巧建立一個空的TempTable以用來儲存所有DB檔案空間的資訊。


--1.建立TempTable儲存資訊。
IF object_id('tempdb..#resultTable') IS NOT NULL
  BEGIN
		DROP TABLE #resultTable
  END

SELECT  
DB_NAME() [資料庫名稱], [name] AS [檔案名稱], physical_name AS [路徑],    
CASE TYPE   WHEN 0 THEN 'Data'  WHEN 1 THEN 'Log' END [檔案類別],   
CASE CEILING([SIZE]/128)    WHEN 0 THEN 1   
ELSE CEILING([SIZE]/128)   END AS  [資料庫檔案全部容量(MB)],  
CASE CEILING([SIZE]/128)    WHEN 0 
THEN (1 - CAST(FILEPROPERTY([name], 'SpaceUsed') AS INT) /128)   
ELSE (([SIZE]/128) - CAST(FILEPROPERTY([name], 'SpaceUsed') AS INT) /128)   
END [資料庫檔案可用容量(MB)] ,    
CASE [is_percent_growth]   WHEN 1 
THEN CAST(growth AS VARCHAR(20)) + '%'  
ELSE CAST(growth*8/1024 AS VARCHAR(20)) + 'Mb'END [檔案成],   							   
CASE [max_size]   WHEN -1 THEN NULL   
			      WHEN 268435456 THEN NULL   
			      ELSE [max_size]   END   [檔案大小上限(MB)]  INTO #resultTable
FROM sys.database_files   
WHERE 1=2
ORDER BY [檔案類別], [FILE_ID]

接著使用sp_MSforeachdb取得所有DB檔案空間的資訊並且新增至TempTable


--2.使用動態SQL搭配sp_MSforeachdb去取的所有資料庫檔案空間的資訊
DECLARE @SQLcmd nvarchar(max)
SET @SQLcmd='
USE ?
INSERT INTO #resultTable
SELECT  
DB_NAME() [資料庫名稱], [name] AS [檔案名稱], physical_name AS [路徑],    
CASE TYPE   WHEN 0 THEN ''Data''  WHEN 1 THEN ''Log'' END [檔案類別],   
CASE CEILING([SIZE]/128)    WHEN 0 THEN 1   
ELSE CEILING([SIZE]/128)   END AS  [資料庫檔案全部容量(MB)],  
CASE CEILING([SIZE]/128)    WHEN 0 
THEN (1 - CAST(FILEPROPERTY([name], ''SpaceUsed'') AS INT) /128)   
ELSE (([SIZE]/128) - CAST(FILEPROPERTY([name], ''SpaceUsed'') AS INT) /128)   
END [資料庫檔案可用容量(MB)] ,    
CASE [is_percent_growth]   WHEN 1 
THEN CAST(growth AS VARCHAR(20)) + ''%'' 
ELSE CAST(growth*8/1024 AS VARCHAR(20)) + ''Mb''END [檔案成],   							   
CASE [max_size]   WHEN -1 THEN NULL   
			      WHEN 268435456 THEN NULL   
			      ELSE [max_size]   END   [檔案大小上限(MB)]  
FROM sys.database_files   ORDER BY [檔案類別], [FILE_ID]'


--在每個資料庫執行@SQL指令
EXEC sp_MSforeachdb @SQLcmd

產生的結果：

結論：使用sys.database_files搭配sp_MSforeachdb取得SQL SERVER上所有資料庫檔案空間使用量的資訊，下一篇將會示範將該文章獲得的結果搭配Database mail與JOB達到自動化管理監控。

回首頁