Showing results for 
Show  only  | Search instead for 
Did you mean: 

Growth of tempDB in Microsoft SQL server

Growth of tempDB in Microsoft SQL server


Original content by Debasish Namata



When IdentityIQ is installed with Microsoft SQL Server as the backing database, IdentityIQ requires ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT to be enabled. (See Configuring IdentityIQ for use with SQL server mirroring) This is also part of the DDL scripts for SQL Server.


This configuration is also known to cause an increase in tempDB usage. This article discusses how to identify what might be causing the tempDB to grow.


What is tempDB used for?

The tempDB is global resource for all databases on the SQL Server. It is used by SQL Server to:

  • Hold temporary user objects that are explicitly created, such as: global or local temporary tables and indexes, temporary stored procedures, table variables, Tables returned in table-valued functions, or cursors.
  • Internal objects that are created by the database engine. These include:
    • Work tables to store intermediate results for spools, cursors, sorts, and temporary large object (LOB) storage.
    • Work files for hash join or hash aggregate operations.
    • Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified), or certain GROUP BY, ORDER BY, or UNION queries.


(See also:


IdentityIQ does not directly make use of the tempdb. Any operations done on it are controlled by SQL Server. But as the above usage shows, there may be queries run by IdentityIQ that can cause objects to be created in the Tempdb. The ultimate size depends on the query and the data being queried. Normally these objects are not very large.


Finding the current tempDB size

The tempDB will grow depending on need, and shrink again when the queries that needed the space are completed, but by default the data file on disk will not shrink, so just looking at the data file does not tell you how large the tempdb currently is. To find the current actual size, you can use the following query, for example:


USE tempdb

EXEC sp_spaceused


Finding out who/what is using the tempDB

To see which queries are using up tempDB space, you need to check while the space is actually being used. A query that can be used for that is:


(t1.internal_objects_alloc_page_count + task_alloc) as allocated,
(t1.internal_objects_dealloc_page_count + task_dealloc) as deallocated,
qt.text as [Batch Text],
qp.query_plan as [Execution Plan]
FROM sys.dm_db_session_space_usage as t1,
    (select session_id,
        sum(internal_objects_alloc_page_count) as task_alloc,
        sum (internal_objects_dealloc_page_count) as task_dealloc
        FROM sys.dm_db_task_space_usage group by session_id) as t2,
    sys.dm_exec_requests t3
        cross apply sys.dm_exec_sql_text (t3.sql_handle) as qt
        cross apply sys.dm_exec_query_plan (t3.plan_handle) as qp
WHERE t1.session_id = t2.session_id
AND t1.session_id >50
AND t1.database_id = DB_ID('tempdb')
AND t1.session_id = t3.session_id
ORDER BY allocated DESC;


If the tempDB keeps growing but you are not sure when it grows, it may be best to run this and the earlier query in a scheduled job every hour or even minute, to record the usage.


Unexpected tempDB usage

If you are seeing unusual or unexpected tempdb usage, please work with your Database Administrator to see which queries are causing it.

Labels (2)

Do IdentityIQ versions( 7.1, 7.2 & 7.3) also require ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT to be enabled. We see that IdentityIQ 8.0 database script has it turned ON by default but no information in lower version of IIQ.  We had an issue yesterday in our environment with the tempdb size where it ran out of space causing application to not work correctly.   


@manjesh_kumar  I would think they do because of the following snippet from the 7.3p3 release notes:

"As of this release, snapshot isolation in SQL Server now is enabled by default in the database upgrade scripts, which matches the database creation scripts. The settings enabled are ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT. This facilitates locking without excessive blocking. As a result of this change, administrators can expect an increase in the utilization of the SQL Server tempdb resource."

However, in a current case we're following up w/ Sailpoint Support where our TempDB version store size grows because of hung DB sessions we've been advised to try to turn the ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT off to see if that helps so I'd say it not 100% mandatory, only so much recommended so that Sailpoint decided to turn this on by default.

>> If you are seeing unusual or unexpected tempdb usage, please work with your Database Administrator to see which queries are causing it.


Our tempdb grows pretty rapidly and the main source are following requests:

update spt_request ...

update spt_identity ...

Is there anything we can do to prevent it?



Version history
Revision #:
3 of 3
Last update:
‎Jun 30, 2023 03:43 PM
Updated by: