--功能說明:概略估算資料庫大小與資料表使用磁碟空間大小
USE 資料庫名稱;
GO
--更新統計資料需謹慎使用,在忙碌的狀態使用將造成系統更為緩慢
--EXEC sp_updatestats;
--GO
--報告和更正目錄檢視中不準確的頁面和資料列計數,在忙碌的狀態使用將造成系統更為緩慢。
--DBCC updateusage(0);
--GO
CREATE TABLE #MyTblInfo
(
[name] nvarchar(256),--資料表名稱
[rows] int,--現有資料列數量
[reserved] varchar(18),--資料表磁碟保留空間大小,資料庫中的物件所配置的空間總量。
[reserved_int] int default(0),--資料表磁碟保留空間大小整數值,資料庫中的物件所配置的空間總量整數值。
[data] varchar(18),--資料表實體資料使用磁碟空間大小,資料所用的空間總量。
[data_int] int default(0),--資料表實體資料使用磁碟空間大小整數值,資料所用的空間總量整數值。
[index_size] varchar(18),--資料表索引使用磁碟空間大小,索引所用的空間總量。
[index_size_int] int default(0),--資料表索引使用磁碟空間大小整數值,索引所用的空間總量整數值。
[unused] varchar(18),--保留給資料表未使用的磁碟空間大小,資料庫中保留給資料庫物件的空間但尚未使用的空間總量。
[unused_int] int default(0)--保留給資料表未使用的磁碟空間大小整數值,資料庫中保留給資料庫物件的空間但尚未使用的空間總量整數值。
);
GO
EXEC sp_MSforeachtable "INSERT INTO #MyTblInfo ([name],[rows],[reserved],[data],[index_size],[unused]) EXEC sp_spaceused '?'";
GO
--從[reserved]、[data]、[index_size]、[unused]取出數字並更新到對應的整數值欄位
UPDATE #MyTblInfo
SET [reserved_int] = CAST(SUBSTRING([reserved], 1, CHARINDEX(' ', [reserved])) AS int),
[data_int] = CAST(SUBSTRING([data], 1, CHARINDEX(' ', [data])) AS int),
[index_size_int] = CAST(SUBSTRING([index_size], 1, CHARINDEX(' ', [index_size])) AS int),
[unused_int] = CAST(SUBSTRING([unused], 1, CHARINDEX(' ', [unused])) AS int);
GO
--顯示資料表狀態資料依照檔案大小由大至小排序
SELECT [name],[rows],[reserved],[reserved_int],
[data],[data_int],[index_size],[index_size_int],
[unused],[unused_int],
CAST([reserved_int]/1024.0 AS VARCHAR) + ' MB' AS 'TABLESIZE',
[reserved_int]/1024.0 AS 'TABLESIZE_FLOAT'
FROM #MyTblInfo
ORDER BY data_int DESC;
GO
--資料表大小
SELECT (CAST(SUM(reserved_int)/1024.0 AS VARCHAR) + ' MB') AS 'RESERVED_FILE_SIZE',
(CAST(SUM(data_int)/1024.0 AS VARCHAR) + ' MB') AS 'DATA_FILE_SIZE',
(CAST(SUM(index_size_int)/1024.0 AS VARCHAR) + ' MB') AS 'INDEX_FILE_SIZE',
(CAST(SUM(unused_int)/1024.0 AS VARCHAR) + ' MB') AS 'UNUSED_FILE_SIZE',
(CAST(SUM(reserved_int)/1024.0 AS VARCHAR) + ' MB')AS 'TOTAL_TABLE_SIZE'
FROM #MyTblInfo;
GO
DROP TABLE #MyTblInfo;
GO
--database_name nvarchar(128) 目前資料庫的名稱。
--database_size varchar(18) 目前資料庫的大小 (以 MB 為單位)。 database_size 包括資料檔和記錄檔。
--unallocated space varchar(18) 資料庫中尚未保留給資料庫物件的空間。
--顯示資料庫大小
EXEC sp_spaceused @updateusage=N'true';
GO

--傳回目前資料庫之相關檔案的實體名稱和屬性。請利用這個預存程序來判斷伺服器所要附加或卸離的檔案名稱。
--name sysname 邏輯檔案名稱。
--fileid smallint 檔案的數值識別碼。 如果指定了 name,就不會傳回這個項目。
--filename nchar(260) 實體檔案名稱。
--filegroup sysname 檔案所屬的檔案群組。NULL = 檔案是記錄檔。 它永遠不在檔案群組中。
--size nvarchar(15) 檔案大小 (以 KB 為單位)。
--maxsize nvarchar(15) 檔案所能成長的大小上限。 這個欄位中的 UNLIMITED 值指出,檔案將成長到磁碟已滿。
--growth nvarchar(15) 檔案的成長遞增。 這表示每次需要新空間時,檔案所增加的空間量。0 = 檔案是固定大小,不會成長。
--usage varchar(9) 資料檔的值是 'data only',記錄檔的值是 'log only'。
EXEC sp_helpfile;
GO
--備註
--database_size 一律會大於 reserved + unallocated space 的總和,因為它包括記錄檔的大小,但 reserved 和 unallocated_space 只考量資料頁。
--保留給資料表未使用的磁碟空間大小整數值 = 資料表磁碟保留空間大小整數值 - 資料表實體資料使用磁碟空間大小整數值 - 資料表索引使用磁碟空間大小整數值
--= [unused_int] = [reserved_int]-[data_int]-[index_size_int]
--將產生的資料寫入暫存資料表#MyTblInfo

, , , ,

K 發表在 痞客邦 PIXNET 留言(0) 人氣()