question

Fatherjack avatar image
Fatherjack 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
tempdbsystem-databasesdatabase-files
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.