搜尋本站文章

2008-01-02

顯示資料庫所使用的磁碟空間、資料實際所使用的空間; 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


請參考以下的執行結果: