sys.master_files does not show accurate size information - by Michael Hotek

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


7
0
Sign in
to vote
ID 377223 Comments
Status Closed Workarounds
Type Bug Repros 5
Opened 10/22/2008 6:37:14 PM
Access Restriction Public

Description

The size column in sys.master_files is supposed to contain the current size of the file in 8K pages. However, the value being reported is the initially allocated size.
Sign in to post a comment.
Posted by DrNotSoEvil on 3/28/2013 at 3:35 AM
I know this is a closed item but I reported this through PSS years ago and even now, in SQL 2008 R2 this is still not fixed.
I saw this only for tempdb but I just found out that on for a filestream data file the size is also incorrect. I assume it should always be 0 since it actually concerns a folder. I now have an instance that's displaying an actual value over 0 in sys.master_files while it's 0 (ok for me) in sys.database_files
Posted by Microsoft on 12/2/2008 at 12:49 PM
Hi,
Your connect item has been resolved because it appears you're working directly with PSS now.

Thanks!
Posted by Peter [MSFT] on 10/31/2008 at 8:04 AM
OK. Please followup with rdorr@microsoft.com.

The fact that one is out of sync is a little odd because both system tables are updated in the same transactions usually.
Posted by Michael Hotek on 10/30/2008 at 5:07 PM
Yes, sys.database_files shows the correct information. It is only sys.master_files that does not. The problem that creates is in gathering database space stats. Since not all databases are accessible to execuute a query against sys.database_files and sys.master_files is rarely accurate, in my experience, the only way to get accurate space information is to make WMI calls to get the file sizes from Windows. That is a pretty bad solution
Posted by Peter [MSFT] on 10/30/2008 at 8:09 AM
Please run the following query on one of the problematic query and E-mail the results to our support team at

rdorr@microsoft.com

use master
go

select @@VERSION
go

exec sys.sp_MSforeachdb 'use [?]; select db_name(), * from sys.database_files; select * from master.sys.master_files where database_id = DB_ID()'
go

Thank you.

Posted by Peter [MSFT] on 10/30/2008 at 7:48 AM
Hi,

So is sys.database_files also inaccurate for these databases? I am checking but as far as I can tell, we really have not had many if any reports of this before. Dev does not have access to MSIT production databases to do any such checks. Support sometimes does.

Thanks.
Posted by Michael Hotek on 10/29/2008 at 5:13 PM
Not all of us start deploying something the day that the product ships. I started deploying SQL Server 2005 at Beta 1, several years before Microsoft even knew what year the product was even going to release. The repro that you are doing isn't going to cause this, because you aren't doing anything to the database. I can't find a single database that has had user activity that is reporting accurately. I have no idea how to repro getting it into this situation, but I do know that this view does not report accurate information in any environment that I have ever looked at, in any service packe, hotfix, or beta of the product. I would be willing to bet that if you looked at the databases that are running Microsoft, you would find more cases where the information isn't accurate than you would find for it being correct.
Posted by Peter [MSFT] on 10/29/2008 at 10:54 AM
Hi,

Given that the view only shipped 3 years ago, 5 years seems like an exageration. I do not know of any other reports of this problem. Of course such reports would probably have gone to the support organization who might have more context on the problem you are seeing.

From my point of view, this works fine and repeatedly. Unless you can help me identify what is different about what you are doing from what I am doing, I do not have much to go on here.

use master
go
CREATE DATABASE testdb ON
(NAME='testdb', FILENAME='c:\testdb.mdf', SIZE=10)
LOG ON
(NAME='testdblog', FILENAME='e:\testdb.ldf', SIZE=4)
GO
USE testdb
go
IF (DB_NAME() = N'master')
RAISERROR('A problem was encountered accessing user DB. Terminating.', 20, 127) WITH LOG
go
ALTER DATABASE testdb MODIFY FILE (NAME='testdb', SIZE=100)
GO
ALTER DATABASE testdb MODIFY FILE (NAME='testdblog', SIZE=10)
GO
USE master
GO
WAITFOR DELAY '00:00:01'
GO
SELECT 'PAGES'=size,
'MB' = size/128,
name
FROM sys.master_files
WHERE database_id = db_id ('testdb')
go
DROP DATABASE testdb
GO

Thank you.

Posted by Michael Hotek on 10/27/2008 at 5:34 PM
For which environment? There hasn't been a single customer site that I've been at in the last 5 years that reported accurate information in this view. That is hundreds of companies spanning tens of thousands of instances and hundreds of thousands of databases. Based on what I've seen, I consider it an anomaly when you actually get correct information from this view.
Posted by Peter [MSFT] on 10/27/2008 at 7:51 AM
At this point, it sounds like you should be working with support to try to get a bug filed. We have tests for this functionality and they seem to be working just fine. I have tried some ad-hoc testing as well and that is working fine. There must be something in your environment that is causing the problem and a Connect entry is not really the way to track that down. You should open a case with Microsoft Support to get this identified and resolved.

Thank you.

Posted by Michael Hotek on 10/25/2008 at 6:34 PM
This was the original size allocated to the database when it was created. The database has been taken offline and then back online, put into emergency mode, backed up, restored, detached, attached, instance stopped/restarted. This is just one of the databases showing this anomaly. I have over a dozen on this particular instance that are not correctly reporting. I also have hundreds of databases spanning dozens of instances at a couple of customers which are not showing the correct size allocation. The only thing consistent is that not a single one shows any update to the space allocated since the database was initially created.
Posted by Peter [MSFT] on 10/24/2008 at 9:53 AM
Hi,

Interesting as these are the default sizes for tempdb files on non-Express SKUs I believe. Were your database files ever this size to begin with?

Can you try setting the database OFFLINE/ONLINE to force a restart and see if the values get updated?

ALTER DATABASE PIC SET OFFLINE
GO
ALTER DATABASE PIC SET ONLINE
GO

Also, is this database and AutoClose DB?

Thank you.
Posted by Michael Hotek on 10/23/2008 at 5:31 PM
In sys.master_files, I see the following for database PIC:
data file - 1024
log file - 64

In sys.database_files for this database, I see the following:
data file - 469528
log file - 6552

This is a test database on my laptop that was restored from a production copy. This instance has been started and stopped hundreds of times. It also rarely has a connection to it since I don't have any current development happening on this database. Additionally, there is very little overall activity on the instance at all.

I understand that there is an asynch update from the grow/shrink, although I really don't understand why since we are only talking about updating a single integer value across possibly a handful of rows, but that's beside the point. I'd expect that there would be code to correct this inconsistency, at a minimum, when a grow/shrink kicks off. It would also be nice to be able to have a command to force a check and correct any inconsistencies. As it stands, sys.master_files is worthless for managing space, because the only way you can guarantee that it is up to date is to restart the instance, which is a really bad requirement. The only way that I've found to reliably get the current disk space used for a database file is to pull the list of files from sys.master_files and then make WMI calls directly to the file system to get the file size. That is a pretty ridiculous work-around.
Posted by Peter [MSFT] on 10/23/2008 at 11:42 AM
Hi,

The updating of sys.master_files occurs in an asynchronous manner from the actual grow/shrink of the file. There are race conditions where a crash might occur and the update not make it to sys.master_files. However, we have code at database startup that is intended to detect and correct such inconsistencies.

Can you clarify what you are seeing in terms of where you see the file size, what sys.master_files shows, and what sys.database_files shows?

Thank you.