Tempdb Data Files are Full:
I’m using a script to create a temporary table to fill up my Tempdb while also conducting some tests locally to see how to resolve the tempdb full issue. I disabled Autogrowth and set the initial sizes for the tempdb data files for testing purposes.
Before running the script to create a table, I checked the Available space on each Tempdb file, 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
I’m currently creating a temporary table with 1000,000,000 rows using a loop. The following is the script I used to populate my Tempdb:
SET NOCOUNT ON
GO— Create temp table
CREATE TABLE #tblToFillTempDBtest (col1 nchar(4000));
GO— Begin loop to insert records
DECLARE @Loop AS INT = 1
Because the values are so low, it is clear from the SS that I have no issues with versionStore and internal objects. Please refer to the Microsoft TechNet page below if the values are excessive.
I identified Active requests using the following query to find out who else is using Tempdb. The requests won’t show up in this Query if the query is finished:
;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 shows how much session space is being used, but if the session is closed, you won’t see any results:
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 script results from above includes the Session ID, SystemNames(Hostname), Username, and the space allotted. I noted the session ID 68 and used the script listed below to identify the offender.
SELECT TEXT FROM sys.dm_exec_connections CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) WHERE session_id = (68)
The space will be returned to the tempdb data files when we ask the user if the Query from the previous script may be stopped.
Note :The method described above is straightforward, but in a real-world setting, you can run into other problems, such as Performance Problems Caused by Tempdb Contention Problems, for which there are alternative troubleshooting procedures.