Sys.Master_Files shows another output then sys.Database_Files

Question

Sys.Master_Files shows another output then sys.Database_Files

Van Heghe Eddy

SSCarpal Tunnel

Points: 4449
More actions
August 17, 2015 at 5:02 am

#302025

Hello,
Does anybody has any good explanation as to why the output from both dmv's does'nt show the same output.
Background:
Running a SQLServer 2012 Always ON env.
I changed the growth interval for a log file on the primary server.
When checking with the Gui, the changes show correct.
However when checking with the query below i got 2 different sizes as a result.
rem: The both represent the same file
SELECT s.name AS [Name] ,
s.file_id AS [ID] ,
s.growth
FROM sys.database_files AS s
WHERE ( s.type = 1 )
ORDER BY [Name] ASC
SELECT DB_NAME([database_id]) AS [Database Name],
[file_id], name, type_desc
,growth
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 4
AND database_id = DB_ID('ERIK_DB') AND type_desc ='LOG'
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);
the first query shows 12800 for growth, which is correct.
The second shows 1280 for growth which is the old value.
Have been searching online but can't find any good explanation.
Many thanks in advance,
Wkr,
Eddy

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Master_files shows the files as of the last time SQL started. Database_files shows the current values.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Van Heghe Eddy SSCarpal Tunnel Points: 4449 More actions · Answer 2

Hi Gail,

Thanks for your answer, and thats what i see happen here also but i wanted to know the reason why.

This because i can't seem to find any 'offical' document who states this.

For example:

Check : https://msdn.microsoft.com/en-us/library/ms186782(v=sql.110).aspx

It states with a note :

When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sys.master_files immediately after dropping or truncating a large object may not reflect the actual disk space available.

This would leave you thinking that the sys.master_files will be updated after a while as opposed to be static since last service start no?

Wkr,

Eddy

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Another place BoL is wrong.

If you look at TempDB as an example, master_files shows the default size for the files (what TempDB starts at), while database_files shows the current size of the files.

The comment about deferred drop is completely misleading, because a drop, deferred or not, does not reduce the size of the files. It reduces the size of the data in the files, increasing the free space, but master_files doesn't show data size or free space, it shows file size

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Van Heghe Eddy SSCarpal Tunnel Points: 4449 More actions · Answer 4

Hi Gail,

Thanks again,

Thats an answer i can live with 🙂

and in fact could even come in handy while checking some data, as you can find the Original size and compare very fast 😀

Wkr,

Eddy