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

栏目: 数据库 · 发布时间: 7年前

内容简介: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

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

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

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

SQL Server使用sys.master_files计算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.sysprufilesmaster.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 来查询!


以上所述就是小编给大家介绍的《SQL Server使用sys.master_files计算tempdb大小不正确》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Web导航设计

Web导航设计

James Kalbach / 李曦琳 / 电子工业出版社 / 2009 年3月 / 69.80元

业务目标的实现,依赖于用户能够找到并使用您提供的服务。本书为您讲述创建有效导航系统的基本设计原则、开发技巧和实用建议,并附有大量的真实案例。本书研究深入,援引广泛,是极佳的参考资料和教学指南,适用于初级和中级网页设计师、产品经理和其他非设计职位,以及寻求全新视角的Web开发老手。一起来看看 《Web导航设计》 这本书的介绍吧!

在线进制转换器
在线进制转换器

各进制数互转换器

图片转BASE64编码
图片转BASE64编码

在线图片转Base64编码工具

MD5 加密
MD5 加密

MD5 加密工具