asked
Inconsistent system database details
Hi, When you have a few minutes free could you please run the two queries below? SELECT [df].name AS [File Name] , [df].size / 128.0 AS [Total Size in MB] , [df].size / 128.0 - CAST(FILEPROPERTY([df].name, 'SpaceUsed') AS INT) / 128.0 AS [Available Space In MB] FROM [sys].[database_files] AS df OPTION ( RECOMPILE ) ; SELECT [mf].[name] AS [file name] , ( [mf].[size] / 128.0 ) AS [Size mb] , [mf].[size] FROM [sys].[master_files] AS mf WHERE [mf].[database_id] = db_id() I am interested in the 2nd column in each recordset, the "Total Size in MB" and "Size mb" columns. For all of my databases they have the same values. Except TempDB. In TempDB I get things like: ![alt text][1] Does anyone have the same scenario? Does anyone know why? [1]: /upfiles/TempDB_DiffSizes.png
Yes to the same scenario. As to why... Googling "master_files size tempdb" pulls back quite a few discussions on the topic, and leads to something that says that this is by design, and the master_files version is the original / restart / starting size of TempDB.
http://connect.microsoft.com/SQLServer/feedback/details/385223/tempdb-space-info-in-master-database-sql-2005
You get the same discrepancies when viewing Reports->Disk Usage, and dB Properties->Files. On a test instance I decided to change the initial size of Temp dB. The log was consuming 109 MB, the initial size said 1 MB. I tried 64 MB - no go... I tried 128 MB and it succeeded. I re-ran your queries and size in MB was then 128 for both. It appears that [sys].[master_files] holds the initial size for SQL Server restart purposes.
