原文: 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 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來查詢！
![Windows下配置Apache的SSL服務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)