SQL TEMPDB

Check

Current size of TEMPDB

USE tempdb

GO

SELECT name, (size*8) as FileSizeKB

  FROM sys.database_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 grows to a maximum size of 2 TB.'

    END,

    growth AS 'GrowthValue',

    'GrowthIncrement' =

        CASE

            WHEN growth = 0 THEN 'Size is fixed.'

            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

https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-2017 

Current Users of TEMPDB

SELECT t1.session_id,

       t1.request_id,

       task_alloc_GB = CAST((t1.task_alloc_pages * 8./1024./1024.) AS numeric(10,1)),

       task_dealloc_GB = CAST((t1.task_dealloc_pages * 8./1024./1024.) AS numeric(10,1)),

       host = CASE WHEN t1.session_id <= 50 

                   THEN'SYS' 

                   ELSE s1.host_name

              END,

       s1.login_name,

       s1.status,

       s1.last_request_start_time,

       s1.last_request_end_time,

       s1.row_count,

       s1.transaction_isolation_level,

       query_text = COALESCE((SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,

                                    (CASE WHEN statement_end_offset = -1

                                          THEN LEN(CONVERT(nvarchar(max),text)) * 2

                                          ELSE statement_end_offset

                                     END - t2.statement_start_offset)/2)

                                FROM sys.dm_exec_sql_text(t2.sql_handle)) , 'Not currently executing'),

       query_plan = (SELECT query_plan

                       FROM sys.dm_exec_query_plan(t2.plan_handle))

  FROM (SELECT session_id,

               request_id,

               task_alloc_pages=SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count),

               task_dealloc_pages = SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count)

          FROM sys.dm_db_task_space_usage

         GROUP BY session_id, request_id) AS t1

  LEFT JOIN sys.dm_exec_requests AS t2

         ON t1.session_id = t2.session_id

        AND t1.request_id = t2.request_id

  LEFT JOIN sys.dm_exec_sessions as s1

         ON t1.session_id=s1.session_id

 WHERE t1.session_id > 50 -- ignore system unless you suspect there's a problem there

   AND t1.session_id <> @@SPID -- ignore this request itself

 ORDER BY t1.task_alloc_pages DESC;

GO

https://littlekendra.com/2009/08/27/whos-using-all-that-space-in-tempdb-and-whats-their-plan/ 

Reconfigure

This example set the TEMPDB data file to 2GB with autogrow increments of 100 MB and the TEMPDB transaction log to 10% of data file with autogrow 100 MB...

USE [master]

GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 2097152KB , FILEGROWTH = 102400KB )

GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 256000KB , FILEGROWTH = 102400KB ) 

Move

ALTER DATABASE [tempdb] MODIFY FILE (NAME = [tempdev], FILENAME = 'T:\SQLTemp\tempdb.mdf');

ALTER DATABASE [tempdb] MODIFY FILE (NAME = [templog], FILENAME = 'T:\SQLTemp\templog.ldf');

You need to restart SQL Server for this to take effect.

IMPORTANT: Make sure the new location exists and is writable by the SQL Server service, otherwise the instance will not restart.If you mess this up the only option is to restart the instance in safe (minimal configuration) mode and correct your paths. (1)

Shrink

Try this, it should shrink TEMPDB leaving 10% free space .

dbcc shrinkdatabase (tempdb, 10)

If this doesn't work (which is common) restart the database instance if possible.

If you cannot restart the database instance (e.g. it's a production instance) then try these steps...

NOTE that these steps are likely to cause a level of database performance degradation similar to that caused by restarting the instance (but without the outage) as both activities result in the need to repopulate caches and buffers.

CHECKPOINT;

GO

DBCC DROPCLEANBUFFERS;

GO

DBCC FREEPROCCACHE;

GO

DBCC FREESYSTEMCACHE ('ALL');

GO

DBCC FREESESSIONCACHE;

GO

DBCC SHRINKFILE (TEMPDEV, 20480);

GO

DBCC SHRINKDATABASE (TEMPDB, 10);

GO

where, in this example, 20480 is the new file size in MB

Bibliography & References