Tempdb Data Files are Full :
I’m constructing a temporary table to fill up my Tempdb using a script, and I’m doing some testing in my local to see how to fix the tempdb full issue. For testing purposes, I set initial sizes for tempdb data files and disabled Autogrowth.
I verified the Available space on each Tempdb file before running the script to build a table, and each one has 99 percent free space:
USE tempDB go -- get data file space and locations SELECT b.groupname AS 'File Group' ,a.NAME ,CONVERT(INT, a.Size / 128.000, 2) AS [Currently Allocated Space (MB)] ,CONVERT(INT, FILEPROPERTY(a.NAME, 'SpaceUsed') / 128.000, 2) AS [Space Used (MB)] ,CONVERT(INT, (a.Size - FILEPROPERTY(a.NAME, 'SpaceUsed')) / 128.000, 2) AS [Available Space (MB)] ,(CONVERT(INT, ROUND((a.Size - FILEPROPERTY(a.NAME, 'SpaceUsed')) / 128.000, 2)) * 100) / (CONVERT(INT, ROUND(a.Size / 128.000, 2))) AS PercentFree FROM sys.database_files a(NOLOCK) LEFT OUTER JOIN sysfilegroups b(NOLOCK) ON a.data_space_id = b.groupid ORDER BY PercentFree
Please see the SS below to run the aforesaid script to check the percent of space available on each tempdb file:
Now I’m using a loop to develop a temporary table with 1000000000 rows. The script I used to fill up my Tempdb is as follows:
SET NOCOUNT ON GO -- Create temp table CREATE TABLE #tblToFillTempDBtest (col1 nchar(4000)); GO -- Begin loop to insert records DECLARE @Loop AS INT = 1 WHILE(@Loop <= 1000000000) BEGIN INSERT INTO #tblToFillTempDBtest VALUES(@Loop) SET @Loop += 1 END
I ran the script in SSMS and noticed that my tempdb was rapidly filling up; after a while, it produced an error stating that the Tempdb Primary filegroup was FULL. Please see below for Error SS:
If we look at the Available Free Space and Percent Free columns from the first script, we can see that all three tempdb data files have ‘0’ percent free space. The following is a screenshot:
We know the suspect Query that is consuming all of the space on tempdb and refusing to be released in this scenario, however in a real-time or production environment, we are unsure of the culprit Query when we have a tempdb full issue..
To determine which files are using or occupying Tempdb space, we use the following query, which will provide the Tempdb utilisation of physical files. Based on the results of the script, we must move to the next step, which is to look for column values of Internal Objects, VersionStore
SELECT SUM(unallocated_extent_page_count) AS [free pages], SUM(unallocated_extent_page_count +user_object_reserved_page_count +internal_object_reserved_page_count +mixed_extent_page_count +version_store_reserved_page_count) * (8.0/1024.0/1024.0) AS [Total TempDB SizeInGB] , SUM(unallocated_extent_page_count * (8.0/1024.0/1024.0)) AS [Free TempDB SpaceInGB] ,unallocated_extent_page_count ,user_object_reserved_page_count ,SUM(version_store_reserved_page_count * (8.0/1024.0/1024.0)) AS [version_store_GB] ,internal_object_reserved_page_count ,mixed_extent_page_count FROM tempdb.sys.dm_db_file_space_usage --where [FreeTempDBSpaceInGB]>50 group by unallocated_extent_page_count,user_object_reserved_page_count,internal_object_reserved_page_count,mixed_extent_page_count;
We can conclude from the above SS that I have no difficulties with versionStore and internal objects because the values are relatively low. If the values are excessive, please see the Microsoft TechNet article below.
To find out who else is using Tempdb, I used the query below to identify Active reQuests. If the Query is done, the reQuests will not appear in this Query.
;WITH task_space_usage AS ( -- SUM alloc/delloc pages SELECT session_id, request_id, SUM(internal_objects_alloc_page_count) AS alloc_pages, SUM(internal_objects_dealloc_page_count) AS dealloc_pages FROM sys.dm_db_task_space_usage WITH (NOLOCK) WHERE session_id <> @@SPID GROUP BY session_id, request_id ) SELECT TSU.session_id, TSU.alloc_pages * 1.0 / 128 AS [internal object MB space], TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space], EST.text, -- Extract statement from sql text ISNULL( NULLIF( SUBSTRING( EST.text, ERQ.statement_start_offset / 2, CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END ), '' ), EST.text ) AS [statement text], EQP.query_plan FROM task_space_usage AS TSU INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK) ON TSU.session_id = ERQ.session_id AND TSU.request_id = ERQ.request_id OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL ORDER BY 3 DESC, 5 DESC
The following query displays the session space usage; however, if the session is closed, you will not receive any results of that Query
SELECT DES.session_id AS [SESSION ID], Db_name(DDSSU.database_id) AS [DATABASE Name], host_name AS [System Name], program_name AS [Program Name], login_name AS [USER Name], status, ( user_objects_alloc_page_count * 8 ) AS [SPACE Allocated FOR USER Objects (in KB)], ( user_objects_dealloc_page_count * 8 ) AS [SPACE Deallocated FOR USER Objects (in KB)], ( internal_objects_alloc_page_count * 8 ) AS [SPACE Allocated FOR Internal Objects (in KB)], ( internal_objects_dealloc_page_count * 8 ) AS [SPACE Deallocated FOR Internal Objects (in KB)], cpu_time AS [CPU TIME (in milisec)], total_scheduled_time AS [Total Scheduled TIME (in milisec)], total_elapsed_time AS [Elapsed TIME (in milisec)], ( memory_usage * 8 ) AS [Memory USAGE (in KB)], CASE is_user_process WHEN 1 THEN 'user session' WHEN 0 THEN 'system session' END AS [SESSION Type], row_count AS [ROW COUNT] FROM tempdb.sys.dm_db_session_space_usage AS DDSSU INNER JOIN sys.dm_exec_sessions AS DES ON DDSSU.session_id = DES.session_id ORDER BY [space allocated for internal objects (in kb)] DESC
The Temp space utilization session ID can be found in the above Query result:
The Session ID, SystemNames(Hostname), Username, and the space allocated may all be found in the above screenshot. I took note of the session ID 68 and used the script below to find the culprit.
SELECT TEXT FROM sys.dm_exec_connections CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) WHERE session_id = (68)
We can ask the user if the Query from the preceding script can be killed; if so, the space will be returned to the tempdb data files.
Note : The above is a simple method to tackle the issue; however, in a live environment, you may encounter additional issues such as Performance Issues due to Tempdb Contention Issues, for which the troubleshooting processes are different.