SQL Server使用sys.master_files計算tempdb大小不正確

一直習慣使用sys.master_files來統計資料庫的大小以及使用情況，但是發現sys.master_files不能準確統計tempdb的資料庫大小信息。如下所示：

SELECT       database_id                                AS DataBaseId 

            ,DB_NAME(database_id)                       AS DataBaseName 

            ,Name                                       AS LogicalName 

            ,type_desc                                  AS FileTypeDesc 

            ,Physical_Name                              AS PhysicalName 

            ,State_Desc                                 AS StateDesc 

            ,CASE WHEN max_size = 0  THEN N'不允許增長'

                  WHEN max_size = -1 THEN N'自動增長'

             ELSE LTRIM(STR(max_size * 8.0 / 1024 / 1024, 14, 2))

                      + 'G'

             END                                    AS MaxSize 

            ,CASE WHEN is_percent_growth = 1

                 THEN RTRIM(CAST(Growth AS CHAR(10))) + '%'

                 ELSE RTRIM(CAST(Growth*8.0/1024 AS CHAR(10))) + 'M'

            END                                          AS Growth 

            ,Is_Read_Only                                AS IsReadOnly 

            ,Is_Percent_Growth                           AS IsPercentGrowth 

            ,CAST(size * 8.0 / 1024 / 1024 AS DECIMAL(8, 4)) AS [Size(GB)]

FROM     sys.master_files

WHERE database_id =2

ORDER BY 1

在Windows視窗里，你會看到這些文件實際大小為18G多，而不是1G大小，而使用sys.master_files統計的Size（GB)僅僅是tempdb文件的初始化大小，當然，你在SSMS裡面使用UI去查看tempdb的屬性發現其大小值又是正確的，

如果你用Profile跟蹤看看具體SQL如下，你會發現，它統計的數據來源於視圖sys.database_files

USE tempdb;

GO

SELECT  s.name AS [Name] ,

        CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS FLOAT) * CONVERT(FLOAT, 8) AS [UsedSpace] ,

        CAST(CASE WHEN s.growth = 0 THEN ( CASE WHEN s.type = 2 THEN 0

                                                ELSE 99

                                           END )

                  ELSE s.is_percent_growth

             END AS INT) AS [GrowthType] ,

        s.physical_name AS [FileName] ,

        s.size * CONVERT(FLOAT, 8) AS [Size] ,

        CASE WHEN s.max_size = -1 THEN -1

             ELSE s.max_size * CONVERT(FLOAT, 8)

        END AS [MaxSize] ,

        s.file_id AS [ID] ,

        'Server[@Name='

        + QUOTENAME(CAST(SERVERPROPERTY(N'Servername') AS sysname), '''')

        + ']' + '/Database[@Name=' + QUOTENAME(DB_NAME(), '''') + ']'

        + '/LogFile[@Name=' + QUOTENAME(s.name, '''') + ']' AS [Urn] ,

        CAST(CASE s.is_percent_growth

               WHEN 1 THEN s.growth

               ELSE s.growth * 8

             END AS FLOAT) AS [Growth] ,

        s.is_media_read_only AS [IsReadOnlyMedia] ,

        s.is_read_only AS [IsReadOnly] ,

        CAST(CASE s.state

               WHEN 6 THEN 1

               ELSE 0

             END AS BIT) AS [IsOffline] ,

        s.is_sparse AS [IsSparse]

FROM    sys.database_files AS s

WHERE   ( s.type = 1 )

ORDER BY [Name] ASC;

sys.database_files的具體定義如下

SET quoted_identifier ON 

SET ansi_nulls ON 

go 

CREATE VIEW sys.database_files 

AS 

  SELECT file_id = f.fileid, 

         file_guid = f.fileguid, 

         type = f.filetype, 

         type_desc = ft.NAME, 

         data_space_id = f.grpid, 

         NAME = f.lname, 

         physical_name = f.pname, 

         state = CONVERT(TINYINT, CASE f.filestate 

                                    -- Map enum EMDFileState to AvailablityStates 

                                    WHEN 0 THEN 0 

                                    WHEN 10 THEN 0 -- ONLINE