SQL Server - tempdb Overview

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server where data resides temporarily.

Best Practices

      1)   Keep Tempdb data and log files on separate disks

      2)   If server has more than 1 processor available, then for 'n' processors create       'n' data files of Tempdb. It will boost the performance of the SQL Server.

     3) Don’t restrict Tempdb data or Log file growth.

     4) Don’t create multiple log files for Tempdb

Tempdb can’t be backed up.

Finding Tempdb data & log file size

Please use the below query to know the size of data & log file of Tempdb. It is important to monitor the size of Tempdb periodically.

USE TempDB

GO

SELECT

      CONVERT(NUMERIC(10, 2), Round(a.size / 128., 2)) AS FileSizeMB,

    CONVERT(NUMERIC(10, 2), Round(Fileproperty(a.NAME, 'SpaceUsed') / 128., 2)) AS UsedSpaceMB,

    CONVERT(NUMERIC(10, 2), Round(( a.size - Fileproperty(a.NAME, 'SpaceUsed') ) / 128., 2)) AS UnusedSpaceMB,

    a.NAME AS DBFileName

FROM   sysfiles a

Query result:

Monitoring Tempdb size will give an idea when a DBA needs to reduce its size with or without restarting SQL Server service.

DBCC SHRINKFILE and/or DBCC SHRINKDATABASE SQL commands are best and only option to reduce the size without restarting SQL Server service.