sys.master_files不能准确展示Tempdb数据库大小

问题提出

老鸟这两周一直没有找到机会略菜鸟,因为菜鸟最近一直在参加百阿培训。这不,刚好菜鸟过完买买买的双十一回来,老鸟逮着机会劈头盖脸问道:“菜鸟啊,你去研究下为什么MSSQL Server系统视图sys.master_files不能准确展示Tempdb数据库数据文件大小啊?”。

场景复盘

菜鸟一脸懵逼:“什么?不准确,不会吧?我一直都是用它来统计数据库文件大小的啊,有什么问题呀?”,菜鸟满脸茫然的开始咨询G哥。
为了便于观察,菜鸟先将Tempdb的数据文件初始大小设置为10MB

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 10MB )
GO

查看tempdb数据库Disk Usage:Right click on tempdb => Reports => Standard Reports => Disk Usage
01.png

创建测试表并插入数据

use tempdb
go

if OBJECT_ID('dbo.test', 'U') is not null
    drop table dbo.test

GO
create table dbo.test
(
    id int identity(1,1) not null primary key
    ,large_column char(5120) not null
)
GO

set nocount on
declare
    @do int = 0
;
while @do < 2000
begin
    INSERT INTO dbo.test
    select REPLICATE(NEWID(), 5120/36)
    
    set @do = @do + 1;
end
GO
exec sys.sp_spaceused test

查看测试表大小为16MB
02.png

通过以下三种方式获取tempdb数据库文件大小:
系统视图:sys.master_files看到是10MB,sys.database_files得到是20MB;
查看Windows物理文件:Windows Explorer看到的是20MB;
GUI:通过GUI看到的Currently Allocated Space也是10MB。
GUI查看数据库空间的方法:
03.png
三种统计方法的对比图
04.png

“哇,果真耶,老鸟不愧是老鸟,对得起这个响当当的名号。但是到底哪个是正确的呢,数据库物理文件大小到底是10MB还是20MB?”,菜鸟带着这个疑问一番翻云覆雨后的研究后,终于知道了事情的真相。

原因分析

sys.master_files: 由于Tempdb是SQL Server每次启动时候重新创建,这个系统视图反应的是tempdb数据库文件的初始大小,MSSQL Server会采用异步方式来更新它。换话句话说,SQL Server每次启动会根据这个系统视图中记录的数据库文件大小来创建Tempdb,当我们手动修改数据库初始文件大小的时候,这个系统视图数据会被更新,比如:

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 10MB )
GO

sys.database_files:这个系统视图展示的数据库文件大小是准确的,会被系统及时更新,但是在使用的时候,需要指定特定的数据库名称。
GUI方式获取数据库文件大小:通过Profiler工具跟踪发现,使用GUI方式实际上是从sys.master_files系统视图中获取的数据,所以对于Tempdb也不准确。
05.png
Windows Explorer:这种方式查看Windows空间占用情况是准确的。

参考链接:
[sys.master_files does not show accurate size information]
(https://connect.microsoft.com/SQLServer/feedback/details/377223/sys-master-files-does-not-show-accurate-size-information)

最后结论

sys.master_files只有在统计tempdb数据库时,无法拿到准确的数据库文件大小;sys.database_files可以拿到准确的数据库文件大小,但是需要指定到特定的数据库下;GUI方式在获取tempdb数据库文件大小背后实际上是通过sys.master_files系统视图获取的,所以,统计也不准确;通过Windows 方式获取的数据库文件大小是准确无误的。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值