TempDB Overflow

TempDB Overflow

One of the most common questions our clients ask is how to clean up TempDB in SQL. TempDB is a system database that contains temporary and system objects, as well as data for sorting and result sets. In this article, we’ll explain how TempDB works and how DBServ diagnoses and addresses TempDB overflows.

How Does TempDB Work?

TempDB stores two types of data:

1. 1. Temporary user objects: These include local user tables in MS SQL Server, created during query execution.
   2. Internal system objects: These are generated by the MS SQL Server core.

A large number of active transactions using TempDB objects can make it difficult, or even impossible, for the system to automatically clear the log and reuse free space. This leads to TempDB overflow, one of the most common issues faced by applications like Microsoft Dynamics AX.

TempDB overflow can cause server issues, potentially leading to an MS SQL Server shutdown. This makes databases on the server inaccessible, resulting in operational paralysis for users.

Diagnosing and Solving Issues

TempDB overflow typically causes two main problems: loss of free disk space due to database growth and reduced performance. In general, addressing these problems involves the following steps:

Insufficient Space

To diagnose TempDB growth in MS SQL, start by monitoring database queries using Extended Events. This server event-handling system helps log all queries on the MS SQL Server, filtering them by duration (we recommend setting a threshold of 3 seconds).

Enable DBMSSQL event logging in the technical journal. Analyzing this journal helps understand the context of queries and pinpoint their origins within 1C.

Performance Degradation

Allocate a dedicated high-speed SSD for TempDB (preferably with an NVMe interface) and monitor its response times.

To prevent persistent TempDB growth, assign an adequate initial size to the database—a best practice for any database.

Below, we outline the diagnostic tools and procedures employed by DBServ.

Viewing Information Using a Custom Procedure

Our custom-developed procedure displays the utilization of TempDB by user objects, SQL Server internal objects, and row versions of active transactions.

1. 1. User Objects. Typically, these are local or global temporary tables or table variables created via user queries. However, as our experience with a cosmetics and perfumery retail network shows, persistent tables can also appear as user objects in TempDB.
   2. Internal Objects. These include sorting, grouping, and hashing objects. They also encompass work tables for cursor or spooling operations, as well as temporary storage for LOB objects.
   3. Row Versioning. If MS SQL TempDB occupies significant space, long or open transactions (in a waiting state) that hinder cleanup may be the cause of uncontrolled growth. The version store holds committed rows to ensure that SELECT operations are not blocked when UPDATE/DELETE operations are performed on the same rows. With versioning enabled (e.g., snapshot isolation with or without row versioning and online index rebuilding), rows are stored in TempDB. Once a transaction is committed, the row is removed from the version store.

General Information on TempDB Utilization

Running the first script provides an overview of TempDB utilization. Below are explanations for the resulting dataset:

• • Usr_obj_gb: Space used by user objects in GB (e.g., temporary tables, cursors). A high value here may indicate a large query initiated by a user.
  • Internal_obj_gb: Space used by SQL Server internal objects in GB (e.g., sorting and grouping results).
  • Version_store_gb: Space occupied by row versions of open transactions. Indicates the presence of long-running transactions.
  • Mixedextent_gb: Space occupied by mixed extents in GB.
  • Free GB: Free space within the SQL Server database in GB.
  • Free %: Free space as a percentage of the database size.
  • Used %: Used space as a percentage of the database size.
  • Total GB: Total size of the TempDB SQL database.

Extended Information on TempDB Utilization

Running the second script provides extended information on TempDB utilization in four result sets:

1. 1. General information (as described above).
   2. Top 10 active transactions using TempDB. A high usr_obj_gb value in the first query may indicate a large query.
   3. Top 10 active transactions and the objects they created in TempDB. Analyze this if internal_obj_gb in the first query is high.
   4. Top 10 active transactions using row versions. Analyze this if version_store_gb in the first query is high.

Preventing TempDB Overflow

Depending on the cause of TempDB overflow, DBServ takes specific measures. Commonly, this involves clearing the TempDB cache, terminating user sessions, and waiting for the secondary replica to catch up.

However, we emphasize that, barring service interruptions, we terminate user sessions only with our clients’ consent.

If no immediate solution is possible, or if the client requires uninterrupted execution, we address the issue by expanding existing files or recreating them on another disk. DBServ engineers specialize in remote administration of SQL Server databases, ensuring your DBMS remains operational and your databases accessible despite TempDB overflow.

Conclusion

This article has explored the inner workings of the MS SQL TempDB global system database, the causes of its overflow, and the monitoring tools utilized by DBServ.

By entrusting your SQL Server database administration to DBServ, you can rely on the expertise of our specialists to diagnose and prevent TempDB overflows effectively, ensuring uninterrupted service and database accessibility. Our proactive approach guarantees that your critical applications and systems continue to operate smoothly, no matter the scale or complexity of the challenges.