Following what @Damien_The_Unbeliever commented, yes those are Table variables
only and if you are still observing them then it's evident that from whichever procedure those got created is still opened (or) not completed yet else those would have already been cleared from TempDB
. To do a test try the code snippet and check against System Databases > tempdb > Temporary Tables:
you will see that the table variable is exist.
declare @tab table(col1 int);
waitfor delay '8:10';
insert into @tab values(123);
Once you cancel the query, it gets cleared from TempDB.
on this question:
How to identify which query is filling up the tempdb transaction log?
you will be able to see a lot more, in different ways and with more technical information as why this is happen and what to do regarding it.
below is a script that I have to check what is filling up the tempDB.
it may be a good starting point for your quest.
use tempdb
SELECT top 100
tsu.internal_objects_alloc_page_count,
tsu.user_objects_alloc_page_count,
es.host_name ,
es.login_name ,
es.program_name,
st.dbid as QueryExecContextDBID,
DB_NAME(st.dbid) as QueryExecContextDBNAME,
st.objectid as ModuleObjectId,
SUBSTRING(st.text, er.statement_start_offset/2 + 1,
(CASE WHEN er.statement_end_offset = -1 THEN
LEN(CONVERT(nvarchar(max),st.text)) * 2 ELSE
er.statement_end_offset
END - er.statement_start_offset)/2) as Query_Text,
tsu.session_id ,
tsu.request_id,
tsu.exec_context_id,
(tsu.user_objects_alloc_page_count
- tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,
(tsu.internal_objects_alloc_page_count
- tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,
er.start_time,
er.command,
er.open_transaction_count,
er.percent_complete,
er.estimated_completion_time,
er.cpu_time,
er.total_elapsed_time,
er.reads,er.writes,
er.logical_reads,
er.granted_query_memory
FROM sys.dm_db_task_space_usage tsu
inner join sys.dm_exec_requests er
ON ( tsu.session_id = er.session_id
and tsu.request_id = er.request_id)
inner join sys.dm_exec_sessions es
ON ( tsu.session_id = es.session_id )
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE (tsu.internal_objects_alloc_page_count
+ tsu.user_objects_alloc_page_count) > 0
ORDER BY (tsu.user_objects_alloc_page_count
- tsu.user_objects_dealloc_page_count)
+(tsu.internal_objects_alloc_page_count
- tsu.internal_objects_dealloc_page_count)
DESC
Best Answer
Thanks for your input. Actually, I found the answer:
Those tables were created by a stored procedure that is set to execute on startup of the database engine service, configured via the
sp_procoption
system stored procedure.