SQL Server 2005中擷取資料庫檔案大小資訊

捕捉SQL Server 2005資料庫大小的趨勢，這是非常重要的，因為DBA們可以利用這些資訊來規劃未來的空間需求，問題的通知類型，並規劃繁重負載的時間段。本文將介紹一種捕捉這一資訊的簡單的方法。

SQL Server上的每個資料庫都包含了關於資料庫檔案的大小的資訊，連同其他一些相關資訊。為了得到這個資訊，我們需要一個方法來從單個數庫中一次性檢索出相關的資料。

有兩個方法可以實現這個功能：

sp_spaceused：這個系統預存程序將返回當前資料庫中的表、索引檢視表或 SQL Server 2005 Service Broker 隊列所使用的磁碟空間。這是預存程序的返回資料庫或者資料庫中表的大小，但是報告的輸出內容不太友好，不好理解。該預存程序通過指令碼來捕獲每個資料庫的資訊，但它可能需要用到使用者自訂的游標。

sp_msforeachdb：這是一個非常有用的系統預存程序，將任何SQL指令碼傳遞給你在每個資料庫上的SQL Server執行個體上來執行。預存程序遍曆每個資料庫，這樣就省卻了您一項項指定資料庫名稱了。本文我們將利用這個預存程序來捕獲各個資料庫檔案大小的資訊。

這些資料我想收集和儲存可以在sys.database_files系統檢視表中獲得，包括資料庫檔案的大小，以及其他一些相關的資訊，如資料庫狀態，資料庫檔案的增長方式(按絕對大小遞增還是按百分比遞增)，是否是唯讀等等。我需要捕獲每個資料庫中的這些資訊。

下面的指令碼以系統檢視表sys.database_files的結構為基礎，建立一個名為DatabaseFiles表(如果它不存在的話)，還增加了一個新的欄位，用來記錄捕獲的記錄什麼時候插入到資料表中。

IF OBJECT_ID('DatabaseFiles') IS NULL
BEGIN
SELECT TOP 0 * INTO DatabaseFiles
FROM sys.database_files
ALTER TABLE DatabaseFiles
ADD CreationDate DATETIME DEFAULT(GETDATE())
END

現在需要填充DatabaseFiles資料表。下面這個指令碼使用sp_msforeachdb預存程序，並通過一個SQL指令碼從sys.database_files讀取資料，並插入到DatabaseFiles資料表中。

EXECUTE sp_msforeachdb 'INSERT INTO DatabaseFiles SELECT *, GETDATE() FROM [?].sys.database_files'

從指令碼中我們可以發現，每個資料庫的資料庫名稱，有點特別：使用[ ?]作為視圖sys.database_files的首碼。

此代碼執行時，實際上是遍曆每個資料庫執行個體，將資料庫名稱作為參數代替[ ?]標記。每個資料庫的資訊，將插入到DatabaseFiles表。這比自己寫一個自訂遊標實現起來容易很多。我還增加了一個GETDATE( )函數，用來記錄記錄插入到表中的時間。

注意：這個例子有點違背編碼通訊協定，使用SELECT *插入到一個表時，沒有明確寫明欄位名稱不列清單。如果用於實際項目中，建議大家作下修改，註明欄位名稱。

為了確保正確捕獲到所有的資料，我們來看看DatabaseFiles資料表中的內容。

SELECT * FROM DatabaseFiles

執行上面的命令後，可以得到類似以下的資訊：

圖1：查看捕獲到的資料庫大小的資料