SQL - TempDB is Full

We already know that the tempdb database is a system database that contains temporary tables created by the server and by the user and always recreated each time SQL Server is restarted. Another points is that tempdb database consists of two parts: the data file(s) in the primary file group, and the tempdb log file(s).
Tempdb information
The tempdb database is a temporary workspace and SQL Server uses tempdb to perform many tasks which are given below:

1. Storage of explicitly created temporary tables
2. Worktables that hold intermediate results that are created during query processing and sorting
3. Materialized static cursors
4. Storage of version records when snapshot isolation levels or read-committed snapshot isolation levels are used

We know that temporary database is the one storing the snapshot of the tables before committing the commands so that on event of roll back, it can be restored. Consequently, it holds lot of space depending on the size of the table and the data to be processed.

In the SQL, we need to pull the data into temporary tables from the database to do more actions to meet the business requirements. These activities are the part of our day to day jobs which are the main root cause for the growing temporary database. Tempdb can grow very large on some systems if a lot of ‘dirty’ queries are run that sluff a lot of data into tempdb. We should be habitual to drop these temp tables after accomplish the tasks.

If our temporary database is full then we should need to look and carefully examine our functions, views, stored procedures and other data components. Test run each of them with analyzer to know how much does it take to query the data needed. If it returns on a longer time than expected, revise the codes to simplify.

How to Find out the correct size of tempdb?

To find the correct size of tempdb files after a shrink operation, execute the following statement in SQL Server Management Studio:

USE TEMPDB GO SELECT NAME, PHYSICAL_NAME, TYPE_DESC, ---- convert into KB ACTUALSIZEKB=(SIZE*8) FROM SYS.DATABASE_FILES NAME PHYSICAL_NAME TYPE_DESC ACTUALSIZEKB tempdev d:datatempdb.mdf ROWS 28070784 templog d:datatemplog.ldf LOG 9027840

Many programmers don’t consider the load of the database server when executing queries which is not a right approach. If you think that your tempDB is growing and taking a lots of the space of your disk then you should move your database to another location because-

1. TempDB grows big and the existing drive does not have enough space.
2. Moving TempDB to another file group which is on different physical drive helps to improve database disk read, as they can be read simultaneously.

Shrink tempdb

We can use to shrink the tempdb database in Microsoft SQL Server where tempdb resets to its configured size after each restart of the instance of SQL Server. A limitation of below method is that it operates only on the default tempdb logical files tempdev and templog. If more files are added to tempdb, you can shrink them after you restart SQL Server as a service. All tempdb files are re-created during start-up.

ALTER DATABASE tempdb MODIFY FILE    (NAME = 'tempdev', SIZE = target_size_in_MB)    --Desired target size for the data file    ALTER DATABASE tempdb MODIFY FILE    (NAME = 'templog', SIZE = target_size_in_MB)    --Desired target size for the log file        /*        Note: The file size, max size cannot be greater than 2147483647 in units of a page size.        The file growth cannot be greater than 2147483647 in units of both page size and percentage.        */

Move TempDB from one drive to another drive

Before move the TempDB to another location, we should make sure that TempDB is set to autogrow and do not set a maximum size for TempDB. We need to check the file size of the tempdb becasue SQL Server locks tempdb while it is expanding and If the current drive is too full to allow autogrow events, then arrange a bigger drive, or add files to TempDB on another device. 

USE master GO ---- change the location of tempdev file ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'd:datatempdb.mdf') GO ---- change the location of templog file ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'd:datatemplog.ldf') GO

The above commands are capable to change the physical location of the tempdb but no changes are made to TempDB till SQL Server restarts. So, we need to stop and restart SQL Server and after that it will create TempDB files in new locations.