SQL Server - How to resolve TempDB get FULL issue.

Please find the complete details to troubleshooting and resolving If the TempDB database get full:-

 Description: The LOG FILE FOR DATABASE 'tempdb' IS FULL.
 Back up the TRANSACTION LOG FOR the DATABASE TO free
 up SOME LOG SPACE

 Reason for temp DB full.
 1. Heavy transaction activity
 2. Due to maintenance job (Index fragmentation etc)
 3. Due to inventory closing or any other such type activity
 4. Mount Drive\ Volume does not have sufficient space to grow temp db log files.
 5. Auto growth is not enable in tempdb
 6. Bulk Operation

 How to check if TempDB database get FULL :-

 

--Log space usage

dbcc sqlperf(logspace)

 

 --Open Tran

dbcc opentran(tempdb)

 

 

-- VLF (Virtual Log File)

  use tempdb

  go

  dbcc loginfo()

 Status - 2 (active log)
 Status - 0 (Inactive Log)

•  To truncate or shrink the log file there should be continuous inactive log.

 Resolution –
1. Simple and effective solution is to re-start the SQL server but in production environment we do not have privilege to restart the SQL services.
 2. We can use below quires to shrink the log file -
 

    dbcc shrinkfile (templog, 0)

 3. Create new log file in some other volume and cab the existing one to stop the auto growth.
 4. Perform failover if it’s in cluster if shrinking will not resolve the issue and we have necessary approvals from business.