How to Troubleshoot Insufficient Disk Spaces in SQL Server tempdb?

The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

1. Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.

2. Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.

3. Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.

4. Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.
   

Operations within tempdb are minimally logged.

This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.

Error Messages

Below are the main Error messages that you can analyze and observer that there is insufficient disk space in the tempdb database.

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 store is forced to shrink because tempdb is full.

3958 or 3966: A transaction cannot find the required version record in tempdb.

SQL Commands/Queries

The following examples show how to determine the amount of space available in tempdb, and the space used by the version store and internal and user objects.

Determining the Amount of Free Space in tempdb :

The following query returns the total number of free pages and total free space in megabytes (MB) available in all files in tempdb.

SELECT SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB] FROM sys.dm_db_file_space_usage;

Determining the Amount Space Used by the Version Store :

The following query returns the total number of pages used by the version store and the total space in MB used by the version store in tempdb.

SELECT SUM(version_store_reserved_page_count) AS [version store pages used], (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB] FROM sys.dm_db_file_space_usage;

Determining the Longest Running Transaction:

If the version store is using a lot of space in tempdb, you must determine what is the longest running transaction. Use this query to list the active transactions in order, by longest running transaction.

SELECT transaction_id FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC; 

A long running transaction that is not related to an online index operation requires a large version store. This version store keeps all the versions generated since the transaction started. Online index build transactions can take a long time to finish, but a separate version store dedicated to online index operations is used. Therefore, these operations do not prevent the versions from other transactions from being removed. For more information, see Row Versioning Resource Usage.

Determining the Total Amount of Space (Free and Used):

The following query returns the total amount of disk space used by all files in tempdb.

SELECT SUM(size)*1.0/128 AS [size in MB] FROM tempdb.sys.database_files

Determining the Amount of Space Used by Internal Objects:

The following query returns the total number of pages used by internal objects and the total space in MB used by internal objects in tempdb.

SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used], (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB] FROM sys.dm_db_file_space_usage;

Determining the Amount of Space Used by User Objects:

The following query returns the total number of pages used by user objects and the total space used by user objects in tempdb.

SELECT SUM(user_object_reserved_page_count) AS [user object pages used], (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB] FROM sys.dm_db_file_space_usage;

Monitoring Space Used by Temp Tables and Table Variables:

You can use an approach similar to polling queries for monitoring the space used by temp tables and temp variables. Applications that acquire a large amount of user data inside temp tables or temp variables can cause space use problems in tempdb. These tables or variables belong to the user objects. You can use the user_objects_alloc_page_count and ser_objects_dealloc_page_count columns in the sys.dm_db_session_space_usage dynamic management view and follow the methods described earlier.

That's all in this post. If you liked this blog and interested in knowing more about SQL Server, Please Like, Follow, Share & Subscribe to www.ImJhaChandan.com .That's all in this post. If you liked this blog and interested in knowing more about SQL Server, Please Like, Follow, Share & Subscribe to www.ImJhaChandan.com .