SQL Server使用sys.master_files计算tempdb大小不正确-阿里云开发者社区

开发者社区> 潇湘隐者> 正文

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

简介: 一直习惯使用sys.master_files来统计数据库的大小以及使用情况,但是发现sys.master_files不能准确统计tempdb的数据库大小信息。如下所示:     SELECT       database_id                                AS ...
+关注继续查看

一直习惯使用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

 

 

clip_image001

 

 

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

 

 

 

clip_image002

 

 

clip_image003

 

 

如果你用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来查询!

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Golang中的Defer必掌握的7知识点
defer作为Golang比较特殊的语法,在实际开发使用过程中会有一些盲区知识点容易让开发者陷入困境之中,本章将详细的罗列冲defer的一些使用场景中细节的问题和案例代码分析。
8 0
node+mysql+express接口开发数据库连接池
node+mysql+express接口开发数据库连接池
11 0
上云第一课第一期部署MySQL数据库
主要讲述数据的部署以及使用
7 0
node+express controller
Node + Express Controller
6 0
教育培训机构使用阿里云无影桌面优缺点
配置灵活,GPU满足设计产品线,价格低廉,随开随用。
10 0
第一期学习报告(Yasso_c)
第一期学习报告(Yasso_c)
16 0
Node + Express + MySQL 接口开发完整案例
Node + Express + MySQL 接口开发完整案例
13 0
ACP实战特训营RDS(DAY3)
要点记录 1. PolarDB的基本概念 1.1、对比单机数据库优势有哪些:简单易用、极致性能、降低成本、海量存储、安全可靠、快速弹性 1.2 、单机数据库容量瓶颈-单机数据库扩展困难-数据库使用成本过高-分布式数据库应用开发繁琐 2. PolarDB产品系列:集群版-单节点-历史库-多主架构 2.1、集群:一个集群包含一个主节点和多个读节点,最多16个节点,即一个主节点和15个只读节点 2.2、地域:是指物理的数据中心,一般情况下,PolarDB集群应该和ECS实例位于同一地域,以实现最高的访问性能
7 0
学习报告 冬季实战营第一期:从零到一上手玩转云服务器
冬季实战营第一期:从零到一上手玩转云服务器-本期学习报告
8 0
云起实验室学习报告之LAMP搭建
云起实验室学习报告之LAMP搭建
6 0
+关注
潇湘隐者
网名潇湘隐者/潇湘剑客、英文名Kerry,兴趣广泛,广泛涉猎,个性随意,不善言辞。执意做一名会写代码的DBA,混迹于IT行业
777
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载