Script:Code to Find the Size and Growth Percentage of the tempdb Data and Log Files

Code to Find the Size and Growth Percentage of the tempdb Data and Log Files

SELECT
  name AS FileName,
  size*1.0/128 AS FileSizeinMB,
  CASE max_size
    WHEN 0 THEN 'Autogrowth is off.'
    WHEN -1 THEN 'Autogrowth is on.'
    ELSE 'Log file will grow to a maximum size of 2 TB.'
  END,
  growth AS 'GrowthValue',
  'GrowthIncrement' =
  CASE
    WHEN growth = 0 THEN 'Size is fixed and will not grow.'
    WHEN growth > 0 AND is_percent_growth = 0
      THEN 'Growth value is in 8-KB pages.'
    ELSE 'Growth value is a percentage.'
  END
FROM tempdb.sys.database_files;
GO

tempdb modify size, move file

Modify the size
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, SIZE = 1024 ).
The database will be resized when the server restarts next.
Move the file
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = 'x:\full\path\xxxx\tempdb\file\tempdb.mdf' /*, SIZE = .... */ ) the file will be created in the new path when the server starts next

Tempdb space lookup queries

Troubleshooting Insufficient Disk Space in tempdbhttp://msdn.microsoft.com/en-us/library/ms176029.aspx

Capacity Planning for tempdb
http://msdn.microsoft.com/en-us/library/ms345368.aspx

Optimizing tempdb Performance
http://msdn.microsoft.com/en-us/library/ms175527.aspx


SELECT
SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]FROM sys.dm_db_file_space_usage;
--current free and used spaceSELECT SUM(size)*1.0/128 AS [size in MB]FROM tempdb.sys.database_files
--Determining the Amount of Space Used by User Objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],(
SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]FROM sys.dm_db_file_space_usage;
--Determining the Amount of Space Used by Internal ObjectsSELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],(
SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]FROM sys.dm_db_file_space_usage;
--Determining the Longest Running TransactionSELECT transaction_idFROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC;--Determining the Amount Space Used by the Version StoreSELECT SUM(version_store_reserved_page_count) AS [version store pages used],(
SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]FROM sys.dm_db_file_space_usage;