SQL Server使用sys.master_files计算tempdb大小不正确

jdgue

一直习惯使用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_filesWHERE database_id =2ORDER BY 1

在Windows窗口里，你会看到这些文件实际大小为18G多，而不是1G大小，而使用sys.master_files统计的Size（GB)仅仅是tempdb文件的初始化大小，当然，你在SSMS里面使用UI去查看tempdb的属性发现其大小值又是正确的，

如果你用Profile跟踪看看具体SQL如下，你会发现，它统计的数据来源于视图sys.database_files

USE tempdb;GOSELECT  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 sWHERE   ( 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                                     WHEN 4 THEN 7 -- DEFUNCT                                     WHEN 5 THEN 3                                     WHEN 9 THEN 3 -- RECOVERY_PENDING                                     WHEN 7 THEN 1                                     WHEN 8 THEN 1                                     WHEN 11 THEN 1 -- RESTORING                                     WHEN 12 THEN 4 -- SUSPECT                                     ELSE 6                                   END),-- OFFLINE          state_desc = st.NAME,          size = Isnull(Filepropertybyid(f.fileid, 'size'), size),          max_size = f.maxsize,          f.growth,          is_media_read_only = Sysconv(bit, f.status & 8),-- FIL_READONLY_MEDIA          is_read_only = Sysconv(bit, f.status & 16),-- FIL_READONLY          is_sparse = Sysconv(bit, f.status & 256),-- FIL_SPARSE_FILE          is_percent_growth = Sysconv(bit, f.status & 32),-- FIL_PERCENT_GROWTH          is_name_reserved = Sysconv(bit, CASE f.filestate                                            WHEN 3 THEN 1                                            ELSE 0                                          END),-- x_efs_DroppedReusePending          create_lsn = Getnumericlsn(f.createlsn),          drop_lsn = Getnumericlsn(f.droplsn),          read_only_lsn = Getnumericlsn(f.readonlylsn),          read_write_lsn = Getnumericlsn(f.readwritelsn),          differential_base_lsn = Getnumericlsn(f.diffbaselsn),          differential_base_guid = f.diffbaseguid,          differential_base_time = NULLIF(f.diffbasetime, 0),          redo_start_lsn = Getnumericlsn(f.redostartlsn),          redo_start_fork_guid = f.redostartforkguid,          redo_target_lsn = Getnumericlsn(f.redotargetlsn),          redo_target_fork_guid = f.forkguid,          backup_lsn = Getnumericlsn(f.backuplsn)   FROM   sys.sysprufiles f          LEFT JOIN sys.syspalvalues st                 ON st.class = 'DBFS'                    AND st.value = f.filestate          LEFT JOIN sys.syspalvalues ft                 ON ft.class = 'DBFT'                    AND ft.value = f.filetype   WHERE  filestate NOT IN ( 1, 2 ) -- x_efs_Dummy, x_efs_Dropped go

sys.master_files的具体定义如下：

SET quoted_identifier ON SET ansi_nulls ON  go  CREATE VIEW sys.master_files AS   SELECT database_id = f.dbid,          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                                     WHEN 4 THEN 7 -- DEFUNCT                                     WHEN 5 THEN 3                                     WHEN 9 THEN 3 -- RECOVERY_PENDING                                     WHEN 7 THEN 1                                     WHEN 8 THEN 1                                     WHEN 11 THEN 1 -- RESTORING                                     WHEN 12 THEN 4 -- SUSPECT                                     ELSE 6                                   END),-- OFFLINE          state_desc = st.NAME,          f.size,          max_size = f.maxsize,          f.growth,          is_media_read_only = Sysconv(bit, f.status & 8),-- FIL_READONLY_MEDIA          is_read_only = Sysconv(bit, f.status & 16),-- FIL_READONLY          is_sparse = Sysconv(bit, f.status & 256),-- FIL_SPARSE_FILE          is_percent_growth = Sysconv(bit, f.status & 32),-- FIL_PERCENT_GROWTH          is_name_reserved = Sysconv(bit, CASE f.filestate                                            WHEN 3 THEN 1                                            ELSE 0                                          END),-- x_efs_DroppedReusePending          create_lsn = Getnumericlsn(f.createlsn),          drop_lsn = Getnumericlsn(f.droplsn),          read_only_lsn = Getnumericlsn(f.readonlylsn),          read_write_lsn = Getnumericlsn(f.readwritelsn),          differential_base_lsn = Getnumericlsn(f.diffbaselsn),          differential_base_guid = f.diffbaseguid,          differential_base_time = NULLIF(f.diffbasetime, 0),          redo_start_lsn = Getnumericlsn(f.redostartlsn),          redo_start_fork_guid = f.redostartforkguid,          redo_target_lsn = Getnumericlsn(f.redotargetlsn),          redo_target_fork_guid = f.forkguid,          backup_lsn = Getnumericlsn(f.backuplsn)   FROM   master.sys.sysbrickfiles f          LEFT JOIN sys.syspalvalues st                 ON st.class = 'DBFS'                    AND st.value = f.filestate          LEFT JOIN sys.syspalvalues ft                 ON ft.class = 'DBFT'                    AND ft.value = f.filetype   WHERE  f.dbid < 0x7fff -- consistent with sys.databases          AND f.pruid = 0          AND f.filestate NOT IN ( 1, 2 ) -- x_efs_Dummy, x_efs_Dropped          AND Has_access('MF', 1) = 1  go

从上面SQL脚本可以看到，统计数据库的大小分别来自于sys.sysprufiles 和master.sys.sysbrickfiles这两个表，然后我们就很难再深入了解具体的原因了。在https://connect.microsoft.com/SQLServer/feedback/details/377223/sys-master-files-does-not-show-accurate-size-information 这个链接里面，对tempdb相关的问题有一些描述：

1. The view sys.master_files is something new and is updated asynchronously. It doesn't updates immediately.

2. When you re-start your SQL Server, SQL Server will re-create tempdb based on sys.master_files.

3. The sys.master_files tell you about any tempdb data file which was there on your system (the number of tempdb files) with which your server have started.

4. While sys.database_files shows currently used tempdb files. Its quite possible that not all tempdb data files came online.

5. You can read the errorlog look for any error meesage did any of the files did not come online.

6. After you started sql server somebody might have executed SQL commands to remove or add tempdb files.

大体意思，sys.master_files中的数据是异步更新的，而不是同步更新的。它不会立即更新。当你重启你的SQL Server时，SQL Server启动时候都会根据sys.master_files中值重新创建、初始化tempdb文件大小。而sys.database_files显示当前使用的tempdb文件。 很可能并非所有的tempdb数据文件都在线。 所以，如果要查询tempdb的准确大小，就要使用sys.database_files来查询！