--============================================================================ --查看數據庫文件大小 SELECT DB_NAME(F.database_id) AS DBName, F.name AS LogicName, F.size*8/1024 AS SizeMB FROM sys.master_files F INNER JOIN sys.databases D ON D.database_id=F.database_id INNER JOIN sys.database_mirroring dm on D.database_id=dm.database_id AND (dm.mirroring_guid IS NULL OR dm.mirroring_role=1) WHERE F.database_id>4 ORDER BY SizeMB DESC --============================================================================

?

--============================================================================ --查看當前實例下各數據庫數據文件中可收縮情況 --UnusedExtents 標示可以被shrink的分區數DROP TABLE #T GO DROP TABLE #T1 GO CREATE TABLE #T (DatabaseID INT,FileID INT,FileGroup INT,TotalExtents INT,UsedExtents INT,LogicName NVARCHAR(200),FilePath NVARCHAR(500) )CREATE TABLE #T1 (FileID INT,FileGroup INT,TotalExtents INT,UsedExtents INT,LogicName NVARCHAR(200),FilePath NVARCHAR(500) )EXEC sp_MSforeachdb N' USE [?] DELETE FROM #T1 INSERT INTO #T1(FileID,FileGroup,TotalExtents,UsedExtents,LogicName,FilePath) EXEC(''DBCC SHOWFILESTATS'')INSERT INTO #T(DatabaseID,FileID,FileGroup,TotalExtents,UsedExtents,LogicName,FilePath) SELECT DB_ID(),FileID,FileGroup,TotalExtents,UsedExtents,LogicName,FilePath FROM #T1 'SELECT DB_NAME(T.DatabaseID) AS DatabaseName, (T.TotalExtents-T.UsedExtents) AS UnusedExtents, * FROM #T AS T ORDER BY UnusedExtents DESC --============================================================================

?

轉載于:https://www.cnblogs.com/TeyGao/p/3542649.html

總結

以上是生活随笔為你收集整理的常用脚本--查看数据库文件大小的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。