SQL SERVER – TempDB is Full

If you come across following errors in SQL SERVER, please follow the steps below.

Event ID: 17052
Description: The LOG FILE FOR DAT

ABASE 'tempdb' IS FULL.
Back up the TRANSACTION LOG FOR the DATABASE TO free
up SOME LOG SPACE

Make sure that TempDB is set to auto growth and do not set a maximum size for TempDB. If the current drive is too full to allow auto growth  events, then arrange a bigger drive, or add files to TempDB on another device (using ALTER DATABASE as described below and allow those files to auto growth.

1.    Check open transaction in TempDb using below query

                           USE TempDB

                           DBCC Opentran

2.    You will get SPID in above query results. Find what is there in SPID using below query

                 DBCC Inputbuffer(SPID)

                            select * from sys.sysprocesses where spid=55

3.  Found no active transaction then proceed to step 4 else step 5

4.  Check space used by each data file by using below query

      

select a.FILEID,[FILE_SIZE_MB] =convert(decimal(12,2), 

round(a.size/128.000,2)),

[SPACE_USED_MB]=convert(decimal(12,2),

round(fileproperty(a.name,'SpaceUsed')/128.000,2)),[FREE_SPACE_MB]=convert(decimal(12,2),

round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)),

NAME = left(a.NAME,15),

FILENAME = left(a.FILENAME,30)

from dbo.sysfiles a

5.  If size used more then prioritize the transaction and kill the open transaction. Once open transaction has been killed then shrink TempDB data file.