Know your TEMPDB

Tempdb is one of the system databases crucial for system performance. Tempdb database is recreated every time SQL server restarts so it does not hold old information once it restarts.

Tempdb is used for the below.

• Temporary objects like local or global temporary, temporary stored procedures, table variables and cursors.
• Internal objects created by SQL Server like i.e work tables, spools.
• Row versions that are generated by data modifications in database snapshot isolation transactions and also when Online re indexing happens.

The following are generally configurations suggested for tempdb and should be changed depending on the needs and the version of SQL Server.

File	Logical name	Physical name	Initial size	File growth
Primary data	tempdev	tempdb.mdf	8 megabytes	Autogrow by 64 MB until the disk is full
Secondary data files*	temp#	tempdb_mssql_#.ndf	8 megabytes	Autogrow by 64 MB until the disk is full
Log	templog	templog.ldf	8 megabytes	Autogrow by 64 megabytes to a maximum of 2 terabytes

* The number of files depends on the number of (logical) cores on the machine. The value will be the number of cores or 8, whichever is lower.

If you have 8 cores having 8 tempdb primary data files might help.

Tempdb running out of space ??

The below list of errors from the SQL server logs will help you to know if you are having insufficient disk space in tempdb.

Error	Is raised when
1101 or 1105	Any session must allocate space in tempdb.
3959	The version store is full. This error usually appears after a 1105 or 1101 error in the log.
3967	The version forced to shrink because tempdb is full
3958 or 3966	A transaction cannot find the required version in tempdb

  

Query:

SELECT SUM(size)*1.0/128 AS [size in MB]

FROM tempdb.sys.database_files

The above query will tell you the size of the tempdb data file. Knowing what’s actually causing the tempdb to grow will help to pinpoint the problem.

  

Quick things can be done:

• The other workaround is to add an extra file. 

• Restart the SQL Server instance will ease the space. 

• Shrinking the tempdb is not suggested but can help. 

As the database size grows you many need to re look at adding more drive space for your  tempdb operations.

Reducing the tempdb size:

Query:

Reducing the tempdb sizes. In my case I would like to set all my tempdb files (Named temdev1,tempdev2…..) to 1 GB. You can do it individually.

DBCC SHRINKFILE (N'tempdev', '1024MB'); --1GB
Or

use master

go

ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev1' ,SIZE = 1024MB ); --1GB

ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev2' ,SIZE = 1024MB ); --1GB

ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev3' ,SIZE = 1024MB ); --1GB

ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev4' ,SIZE = 1024MB ); --1GB

ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev5’ ,SIZE = 1024MB ); --1GB

ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev6' ,SIZE = 1024MB ); --1GB

ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev7' ,SIZE = 1024MB ); --1GB

ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev8' ,SIZE = 1024MB ); --1GB

Read more about tempdb