Tempdb Full troubleshoot

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.

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms176029(v=sql.105)?redirectedfrom=MSDN

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.

Author: Sri

Hello Friends, This blog is to help the IT professionals who want to become a professional SQL Server DBA but don't know how and from where to start with. So, I am going to share my experiences and my learning in this blog. Will talk about what are the pre-requisite skills required to become a Professional SQL Server DBA, how much time it takes to be a good DBA and what are the additional skill sets are required to become a good DBA. Apart from that I will also post real time sql server settings on server level and database level, Configuring High Availability. Also will share the Client requirements with real time setups if possible I will provide you with screenshots.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.