How to Troubleshoot Tempdb issues without restarting SQLServices

TEMPDB:

Whenever Tempdb gets full and there is no space on tempdb drive…… it may not allow new connections through SSMS try following steps to put the SQL back to normal state.

1.         Try in connecting through Query Analyzer

2.         Check any live transactions are going on : DBCC Opentran(‘tempdb’) and check what spid is doing (- DBCC inputbuffer(SPID)), if it is long running query and try to kill it – Kill SPID

3.        If there is no long running query found in step-2 then issue check point by running following query, this will flushes out the committed transactions

Use Tempdb
Checkpoint

4.        After step-3 the SQL will begin to respond normally and now you need to shrink the log/data file accordingly

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE   (NAME = templog,   SIZE = 1280MB)
GO

5.        Sometimes nothing will work in that case try below code  if this doesn’t work then the final option  is restarting SQL services

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
DBCC SHRINKFILE (tempdev16,18250)
Go

Following are the scenarios where in Tempdb will be used in.

Ø  Any sorting that requires more memory than has been allocated to SQL Server will be forced to do its work in tempdb;
 

Ø  If the sorting requires more space than you have allocated to tempdb, one of the above errors will occur;
 

Ø   DBCC CheckDB('any database') will perform its work in tempdb -- on larger databases, this can consume quite a bit of space;
 

Ø  DBCC DBREINDEX or similar DBCC commands with 'Sort in tempdb' option set will also potentially fill up tempdb;
 

Ø  Large resultsets involving unions, order by / group by, cartesian joins, outer joins, cursors, temp tables, table variables, and hashing can often require help from tempdb;
 

Ø  Any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb;