Setting Limits for tempdb Space Usage

The tempdb_space resource limit restricts the number of pages a tempdb database can have during a single session. If a user exceeds the specified limit, the session can be terminated, or the batch or transaction aborted.

For queries executed in parallel, the tempdb_space resource limit is distributed equally among the parallel threads. For example, if the tempdb_space resource limit is set at 1500 pages and a user executes the following with three-way parallelism, each parallel thread can create a maximum of 500 pages in tempdb:

select into #temptable from partitioned_table

The system administrator or database administrator sets the tempdb_space limit using sp_add_resource_limit, and drops the tempdb_space limit using sp_drop_resource_limit.