SQL Server 2016 TempDB physical file size is different than SQL System Catalog And Proportional Fill Algorithm is not working as expected
0
votes
1
answer
418
views
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?
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);
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?



Asked by sachin-SQLServernewbiee
(473 rep)
Feb 11, 2020, 06:47 AM
Last activity: Mar 2, 2025, 06:04 PM
Last activity: Mar 2, 2025, 06:04 PM