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

  . 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 Shrink TempDB data file.