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:
       physical_name AS CurrentLocation 
FROM   sys.master_files 
WHERE  database_id = Db_id(N'tempdb'); 

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
EXEC sp_helpfile

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;
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');

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)

Interested in working with me? I can be reached at pbaniya04[at] for any questions, consulting opportunities or you may drop a line to say HELLO. Thank your again for visiting my blog and looking forward to serving you more.

Have a Database-ious Day!

No comments

Powered by Blogger.