ways to reduce the size of the tempdb database in SQL Server without restarting the server

 

 

Clearing TempDB:  You can clear the contents of the tempdb database by running the following command:

DBCC FREEPROCCACHE;

DBCC DROPCLEANBUFFERS;

This will remove all cached data and stored procedures from memory, which will reduce the size of tempdb.

Reduce the number of active sessions: The tempdb database is used to store temporary data for all active sessions. You can reduce the number of active sessions by killing any inactive or idle sessions.

 

Check for long-running transactions: Long-running transactions can cause the tempdb database to grow in size. You can check for long-running transactions by using the following query:

SELECT

     at.transaction_id,

     at.name,

     at.transaction_begin_time,

     DATEDIFF(minute, at.transaction_begin_time, GETDATE()) AS 'Duration',

     at.isolation_level,

     ar.session_id,

     ar.database_id,

     ar.command,

     aa.start_time,

     DATEDIFF(minute, aa.start_time, GETDATE()) AS 'ActiveDuration',

     aa.total_elapsed_time/1000 AS 'TotalElapsedSeconds'

 FROM

     sys.dm_tran_active_transactions at

 LEFT OUTER JOIN

     sys.dm_tran_active_transactions aa ON aa.transaction_id = at.transaction_id

 LEFT OUTER JOIN

     sys.dm_exec_sessions ar ON ar.session_id = aa.session_id

 ORDER BY

     aa.start_time DESC;

You can then take the necessary action to either commit or rollback any long-running transactions.

 

Adjust TempDB file size: If you find that the tempdb database is still growing in size, you can increase the file size of tempdb by adding additional data files. This can be done using the ALTER DATABASE command. However, this approach will require some downtime and may not be possible without restarting the server.