學海無邊，書囊無底

方法1：使用　DBCC showfilestats with no_infomsgs  計算 mdf 總容量與使用容量

                        DBCC SQLPERF(logspace)   計算 ldf 總容量與使用容量

CREATE TABLE #SpaceUsage
(
Fileid INT ,
FileGroup smallint,
TotalExtents int,
UsedExtents int ,
databaseName SYSNAME,
FileName varchar(max),
logDate DATETIME DEFAULT GETDATE(),
)
GO
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; 
INSERT INTO #SpaceUsage(Fileid,FileGroup, TotalExtents, UsedExtents, databaseName,FileName)
 EXEC (''DBCC showfilestats with no_infomsgs'')
'

INSERT INTO #logSpaceUsage
(databaseName, logSize, logSpaceUsed, [status])
EXEC ('DBCC SQLPERF(logspace)')


SELECT databaseName,(TotalExtents*64)/1024.0 TotalMB,(UsedExtents*64)/1024.0 UsedMB
,FileName,logDate  FROM #SpaceUsage

GO
DELETE #SpaceUsage

GO

 

 

方法2 : 利用 FileProperty系統函數計算mdf 使用量 (使用條件 ：SQL 2005 版本以上)

IF object_id('tempdb..#SpaceUsage') IS NOT NULL  
	BEGIN  
		 DROP TABLE #SpaceUsage
	END
CREATE TABLE #SpaceUsage
(
name SYSNAME ,
TotalMB int,
UsedMB int ,
Type_Desc NVARCHAR(10),
FileName varchar(max),
logDate DATETIME DEFAULT GETDATE(),
)
GO
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; 
INSERT INTO #SpaceUsage(name, TotalMB, UsedMB,Type_Desc, FileName)
 SELECT  f.name  
	,CONVERT(decimal(10,3),(f.size) / 128) AS TotalMB
	,CONVERT(decimal(10,3),(FileProperty(f.name, ''SpaceUsed''))/128) AS UsedMB
	, f.Type_Desc
	, f.physical_name 
    FROM sys.database_files f
    LEFT JOIN sys.filegroups g on f.data_space_id = g.data_space_id
'

SELECT * FROM #SpaceUsage

 

 

方法 3：利用檢視表sys.partitions sys.allocation_units 取得mdf使用的page數量 select sum(a.total_pages) * 8192 / 1048576.0 AS UsedMB from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id

USE master
GO
CREATE TABLE SpaceUsed_MDF(
	[ServerName] [nvarchar](128) NOT NULL,
	[DBName] [nvarchar](128) NOT NULL,
	[Name] [nvarchar](132) NOT NULL,
	[Type] [varchar](4) NOT NULL,
	[TotalMB] [decimal](10, 3) NULL,
	[UsedMB] [decimal](10, 3) NULL,
	[FileName] [varchar](300) NULL,
	[FileGrowMB] [decimal](10, 3) NULL,
	[LogDate] [date]  DEFAULT (getdate())	
) ON [PRIMARY]

GO

CREATE PROC [dbo].[usp_MDT_SpaceUsed_DBFile] 
@SERVERNAME  nvarchar(128)
AS

--DECLARE @SERVERNAME nvarchar(128) ='Sandy-PC'
IF object_id('tempdb..#DBName') IS NOT NULL  
	BEGIN  
		 DROP TABLE #DBName
	END 
create table #DBName(DBName nvarchar(128))                   


DECLARE @TSQL NVARCHAR(MAX)=''
 SET @TSQL ='SELECT * FROM OPENROWSET(''SQLNCLI'', ''Server=#SERVERNAME#;Trusted_Connection=yes;'',
 ''SET FMTONLY OFF  select name from [sys].[databases] WHERE state_desc=''''ONLINE'''' '') a'
 SET @TSQL = REPLACE (@TSQL , '#SERVERNAME#' ,@SERVERNAME )
 INSERT INTO #DBName
 EXEC  (@TSQL)


DECLARE @DBName nvarchar(128)
DECLARE curtbls CURSOR
FOR
 SELECT DISTINCT DBName  FROM #DBName
 open curtbls
 
fetch next from curtbls into @DBName
while @@fetch_status=0
begin
    --DECLARE @TSQL NVARCHAR(MAX)='' DECLARE @SERVERNAME NVARCHAR(128)='Sandy-PC' DECLARE @DBNAME NVARCHAR(128)='Northwind'
	 SET @TSQL ='SELECT '''+@SERVERNAME +''',* FROM OPENROWSET(''SQLNCLI'', ''Server=#SERVERNAME#;Trusted_Connection=yes;'',
	 ''SET FMTONLY OFF  
	  SELECT  ''''#SERVERNAME#'''',f.name  AS LogicalName ,f.Type_Desc   
    , f.physical_name AS PhysicalName 
	,CONVERT(decimal(10,3),(f.size) / 128) AS TotalMB
	/*,CONVERT(decimal(10,3),(FileProperty(f.name, ''''SpaceUsed''''))/128) AS UsedMB*/
	,(select  sum(a.total_pages) * 8192 / 1048576.0  AS  UsedMB        
			from #DBNAME#.sys.partitions p  
			join #DBNAME#.sys.allocation_units a on p.partition_id = a.container_id) AS UsedMB	
    FROM #DBNAME#.sys.database_files f
    LEFT JOIN #DBNAME#.sys.filegroups g on f.data_space_id = g.data_space_id
	   WHERE f.type_desc=''''ROWS'''''') a'
	 SET @TSQL = REPLACE (@TSQL , '#SERVERNAME#' ,@SERVERNAME )
	 SET @TSQL = REPLACE (@TSQL , '#DBNAME#' ,@DBNAME )
	 INSERT INTO master.dbo.SpaceUsed_MDF(ServerName,DBName  ,Name ,type,FileName,TotalMB,UsedMB )
	 EXEC  (@TSQL)
	 --PRINT @TSQL
	
    
fetch next from curtbls into  @DBName
end
close curtbls
deallocate curtbls
GO

EXEC usp_MDT_SpaceUsed_DBFile 'Localhost'
SELECT * FROM master.dbo.SpaceUsed_MDF