TempDB is full

Whenever you get a Temp DB full alert notification, either you move the temp db location to where you have enough disk space or add more space to an existing disk. What happens when temp db is full? Temp DB is where database engine runs all the join, sort, calculation and much more operations before sending the query result back to the requester. Temp database can impact the performance of query execution if not allocated properly or is full.

Let's see how we can fix this issue. First, find out the existing location and number of Temp Data files in your SQL Instance. Sometime you have more then one temp db files.

SQL Syntax:

SELECT NAME, 
       physical_name AS CurrentLocation 
FROM   sys.master_files 
WHERE  database_id = Db_id(N'tempdb'); 

Output:

Find out the temp db file configurations. This gives your the max size and if auto growth is enabled or not. This is also the alternatives to above query.

SQL Syntax

USE TempDB
GO
EXEC sp_helpfile
GO

Output:

Moving  Temp-db File Location:

To move tempdb file location to new location, just alter the location, you don't need to move the files physically, see note below as why!

SQL Syntax:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
GO

Observation: File Type in SQL Server:

•   mdf: Master data file.
•   ldf: Log Data file.
•   ndf: Named data file.

Stop and Restart: The instance of a SQL Server Instance. Restarting will make the change into effect. Verify the files are using new locations and the configurations remains the same as before using the query below. You may delete the those files from previous locations only after you restart your instance.

SELECT name, physical_name AS CurrentLocation, state_desc
  FROM sys.master_files
      WHERE database_id = DB_ID(N'tempdb');

Note: Because tempdb is re-created each time the SQL Server service is started, you do not have to physically move the data and log files. The files are created in the new location when the service is restarted in step 3. Until the service is restarted, tempdb continues to use the data and log files in existing location. Once you have restarted the SQL Server service, you may want to delete the old tempdb data and log files from the original location. (msdn)