Tempdb not using all files

All replies

• 

  

  0

  Sign in to vote

  Hi andremg,
  
  >>What's the best way to see if I'm correct?  And is this really possible, that tempdb would only use the first data file?
  
  Using sys.database_files to check the TempDB database files usage as below T-SQL.

  USE tempdb; 
  GO
  Select  Name AS DBFileName, file_id AS DBFileID,
      (size * 8.0/1024) as FileSizeMB,
      ((size * 8.0/1024) - (FILEPROPERTY(name, 'SpaceUsed') * 8.0/1024)) As FileFreeSpaceMB,
  	 cast((((size * 8.0/1024) - (FILEPROPERTY(name, 'SpaceUsed') * 8.0/1024))/(size * 8.0/1024))*100 as decimal(6,2)) as FreeSpacePercent
  From sys.database_files
  

  The primary database file with ID=1 contains metadata information about the database, so that it has the least free.
  
  Make sure that the TempDB files have equal size within the same filegroup to provide the highest possible degree of parallel operations efficiency, due to the Proportional Fill writing mechanism that is used to write the data inside the data files. Please check this from TempDB properties. Right click TempDB > Properties > Files
  

  

  Best regards,
  Cathy 
  

  ---

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

  • Marked as answer by andremg Thursday, July 23, 2020 4:07 AM

  Thursday, July 23, 2020 2:47 AM
• 

  

  0

  Sign in to vote

  Thank you Cathy, I think you hit the nail on the head.  For some reason our first file is not sized the same as the others.  We'll correct this at our next reboot.  I appreciate the great reply.

  ---

  André

  Thursday, July 23, 2020 4:07 AM
• 

  

  0

  Sign in to vote

  Note that the GUI show you the current size, not the initial size (as created when you start your SQL Server). Is is possible that the initial size is equal for all files, and only the first file has grown since startup. Not likely since the primary filegroup has "autorow all files" set for tempdb since 2016. Verify by checking sys.master_files. See: http://sqlblog.karaszi.com/managing-tempdb/

  ---

  Tibor Karaszi, SQL Server MVP (Web Blog)

  Monday, August 3, 2020 7:08 AM