0

When I see size of file physically on drive, it is different for one of the tempdb secondary data file when compared to size mentioned in system catalog. What can the reason for it?enter image description here

enter image description here

In tempdb properties we are also seeing same thing as we say via Query. So query should not be an issue.

SELECT f.name AS [File Name] , 
       f.physical_name AS [Physical Name], 
       CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB], 
       CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) AS [Available Space In MB], 
       [file_id], 
       fg.name AS [Filegroup Name],
       (f.growth*8)/1024 AS [AutoGrowth In MB] 
FROM sys.database_files AS f WITH (NOLOCK) 
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK) 
  ON f.data_space_id = fg.data_space_id 
OPTION (RECOMPILE); 

enter image description here

Also another major issue is proportional fill algorithm is not working as expected as couple of files are having more size when compared to other files. What can be the reason for it?

1
  • Could you add the query used? Commented Feb 11, 2020 at 7:01

1 Answer 1

0

Also another major issue is proportional fill algorithm is not working as expected as couple of files are having more size when compared to other files. What can be the reason for it?

One reason for this could be that the files were intentionally (mis)configured like this.

Try querying sys.master_files as well, which will have the last configured size value for the tempdb files:

SELECT 
    [name], 
    [file_id], 
    [type_desc], 
    [size] * 8 / 1024 [size_mb]
FROM sys.master_files
WHERE database_id = 2

You could manually shrink the main tempdb file to 350 MB to get the sizes allocations spreading around normally again.


Regarding your other question, I'm really not sure why the value in sys.database_files is different from what is shown in the file explorer. Hopefully someone else can come along and solve that mystery.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.