顯示資料庫所使用的磁碟空間、資料實際所使用的空間; Displays the disk space reserved and used by the whole database

我們可以使用：

sp_spaceused

可以用於：顯示資料列的數目、所保留的磁碟空間和資料表所用的磁碟空間、索引檢視，或目前資料庫中的 SQL Server 2005 Service Broker 佇列，或顯示整個資料庫所保留和使用的磁碟空間。

重要引數：
@updateusage：
指出應該執行 DBCC UPDATEUSAGE 來更新空間使用方式資訊。當未指定 objname 時，會針對整個資料庫執行這個陳述式；
否則，就會針對 objname 來執行這個陳述式。值可以是 true 或 false。updateusage 是 varchar(5)，預設值是 false。

提醒：
database_size 一律會大於 reserved + unallocated space 的總和，因為它包括記錄檔的大小，但 reserved 和 unallocated_space 只考量資料頁。

XML 索引和全文檢索索引所用的頁面包括在兩個結果集的 index_size 中。當指定了 objname 時，reserved 和 index_size 總結果也會將物件的 XML 索引和全文檢索索引的頁面計算在內。

當指定了 updateusage 時，SQL Server 2005 Database Engine 會掃描資料庫中的資料頁，關於每份資料表所用的儲存空間，它會進行 sys.allocation_units 和 sys.partitions 目錄檢視的必要更正。

例如，在某些狀況下，在卸除索引之後，資料表的空間資訊可能不是目前的資訊。
如果是大型資料表或資料庫，updateusage 的執行時間可能會比較長。
請只在您覺得傳回的值不正確時，以及不會對資料庫的其他使用者或處理序造成不良影響時，才使用 updateusage。
如果願意的話，您可以個別執行 DBCC UPDATEUSAGE。

當您卸除或重建大型索引時，或卸除或截斷大型資料表時，Database Engine 會延遲取消配置實際的頁面及其相關聯鎖定，直到認可交易之後。延遲的卸除作業並不會立即釋出已配置的空間。
因此，在卸除或截斷大型物件之後，sp_spaceused 立即傳回的值不一定能反映實際可用的磁碟空間。

請參考以下的範例：

USE AdventureWorks;
GO
-- 檢視目前資料庫所用的空間，但不一定能反映實際可用的磁碟空間。
EXEC sp_spaceused
GO
-- 要求掃描資料庫中的全部的資料頁後，再檢視目前資料庫所用的空間
EXEC sp_spaceused @updateusage = N'TRUE';
GO

請參考以下的執行結果：

所使用我們在使用 sp_spaceused 時，將會面臨幾個問題：

• 沒有使用 @updateusage = N'TRUE'，顯示出來的實際可用的磁碟空間可能不正確。
• 使用 @updateusage = N'TRUE' 可以顯示實際可用的磁碟空間，卻當下額外耗用系統資源。
• 預存程序顯示的結果集，不容易再利用。
• 未分別資料檔案與交易記錄檔案之空間使用。
  等等...

可能有幾種方式可以處理這問題，筆者以 DBCC showfilestats 這個 undocumented command 來討論：
請參考以下的範例：

USE AdventureWorks
GO
DBCC showfilestats

請參考以下的執行結果：




TotalExtents：指所使用的磁碟空間 Extents(64KB)
UsedExtents：指資料實際所佔用的空間 Extents(64KB)

筆者整理後，請參考以下的範例：
檢視單一資料庫，各個資料檔案的硬碟使用空間與資料實際使用空間

USE AdventureWorks
GO
SET NOCOUNT ON
DECLARE @tDBSize TABLE
(DBName SYSNAME DEFAULT(DB_NAME()),
Fileid INT, FileGroup INT, TotalExtents INT,
UsedExtents INT, Name SYSNAME, FileName NVARCHAR(4000)) 

INSERT @tDBSize(Fileid,FileGroup,TotalExtents,UsedExtents,Name,FileName)
EXEC ('DBCC showfilestats')

SELECT DBName N'資料庫',Name N'資料檔案',
TotalExtents*64.0/1024 N'資料檔案使用硬碟空間(MB)',
UsedExtents*64.0/1024 N'資料實際使用空間(MB)', FileName N'實體檔案路徑'
FROM @tDBSize
GO

請參考以下的執行結果：




筆者整理後，請參考以下的範例：
檢視單一資料庫，各個[資料檔案]、[交易記錄檔]的硬碟使用空間與資料實際使用空間

USE AdventureWorks
GO
SET NOCOUNT ON
DECLARE @tDBSize TABLE
(DBName SYSNAME DEFAULT(DB_NAME()),
Fileid INT, FileGroup INT, TotalExtents INT,
UsedExtents INT, Name SYSNAME, FileName NVARCHAR(4000)) 

DECLARE @tLogSize TABLE
(DBName sysname, logsize float, used float, status int)

INSERT @tDBSize(Fileid,FileGroup,TotalExtents,UsedExtents,Name,FileName)
EXEC ('DBCC showfilestats')

INSERT INTO @tLogSize
EXECUTE ('DBCC SQLPERF(LOGSPACE)')

SELECT D.DBName N'資料庫',D.Name N'資料檔案',
TotalExtents*64.0/1024 N'資料檔案使用硬碟空間(MB)',
UsedExtents*64.0/1024 N'資料實際使用空間(MB)',
logsize N'交易記錄檔整體使用的硬碟空間(MB)',
(logsize*used/100) N'交易記錄檔整體的實際使用(MB)'
FROM @tLogSize L INNER JOIN @tDBSize D
ON L.DBName=D.DBName

請參考以下的執行結果：