Adaptive Server version 12.5 provides the tempdb_space resource limit, which 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.
tempdb_space is similar to other resource limit types such as io_cost, row_count, and elapsed_time, that help System Administrators (SAs) and Database Administrators (DBAs) prevent queries and transactions from monopolizing server resources. Resource limits are described in detail in Chapter 18, “Limiting Access to Server Resources,” in the System Administration Guide.
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 SA or DBA sets the tempdb_space limit using sp_add_resource_limit, and drops the tempdb_space limit using sp_drop_resource_limit.
The function and scope of tempdb_space is as follows:
Limit type |
Use for queries that |
Measuring resource using |
Scope |
Enforced during |
|---|---|---|---|---|
tempdb_space |
Use all space in tempdb when creating work or temporary tables. |
Number of pages used in tempdb per session. |
Query batch or transaction |
Execution |
