September 16, 2014 at 11:58 am
Tempdb on my database grew 50+GB, it was 4-5 GB before. How can I check what caused that grow? Now the size of the TempDB around 60GB. Will this size stay 60GB?. Now What should (should I?) I do to bring back to 5 GB without restarting SQL?
September 16, 2014 at 12:49 pm
If that happened recently then you can try get some information from the default trace. But it won't give you a sql statement which caused it, you will be able to get only datetime, login and app name.
DECLARE @filename VARCHAR(500)
SELECT @filename = SUBSTRING(path, 0,LEN(path) - CHARINDEX('\',REVERSE(path)) + 1)+ '\Log.trc'
FROM sys.traces
WHERE is_default = 1 ;
SELECT
te.Name AS EventName
,StartTime
,NTDomainName
,NTUserName
,LoginName
,ApplicationName
,HostName
,DatabaseName
,Filename
,IntegerData/128 [Size MB]
,CAST(Duration/1000000. AS DECIMAL(20,2)) AS [Duration sec]
,EndTime
,SPID
,SessionLoginName
,Error
,Success
,IsSystem
FROM fn_trace_gettable(@fileName, DEFAULT) gt
INNER JOIN sys.trace_events te ON EventClass = te.trace_event_id
WHERE EventClass IN(92, 93) -- Data/Log File Auto Grow
AND DatabaseName = 'tempdb'
ORDER BY StartTime DESC
October 26, 2014 at 9:36 am
Thanks!!! Sorry for the late reply
October 27, 2014 at 7:29 am
The following can be used to identify what objects are currently consuming space in tempdb (the name of each temp table and it's size) and what sessions are allocating the most space (allocated / deallocated mb). However, for this detail, you must run these while the tempdb space is still actively allocated. The most likely suspects are stored procedures dumping millions of rows into a temp table or a non-indexed hash join between large tables resulting in SQL Server creating temporary hash tables in the background.
Your tempdb should be located on storage sperately from data files, so there is plenty of room to grow and no contention for I/O and space with data files. Files don't shrink by themselves and it's best to leave them allocated as is, because growing the file is a performance hit.
-- query allocated temp tables with record count and size.
use tempdb;
select o.type_desc
, substring(o.name,1,charindex('__',o.name)+1)+right(o.name,12) table_shortname
, si.name index_name
, case si.index_id when 0 then 'HEAP' when 1 then 'CLUSTERED' else 'NONCLUSTERED' end index_type
, row_count
, ((reserved_page_count * 8024) / 1024 / 1024)reserved_mb
, create_date
from sys.dm_db_partition_stats ps
left join sys.objects o on ps.object_id = o.object_id
left join sys.indexes si on si.object_id = o.object_id and si.index_id = ps.index_id
where is_ms_shipped = 0
order by reserved_page_count desc;
-- query tempdb allocation by session:
select *, (allocated_mb - deallocated_mb)reserved_mb from
(
select session_id
,(((sum(internal_objects_alloc_page_count + user_objects_alloc_page_count)*8024)/1024)/1024) as allocated_mb
,(((sum(internal_objects_dealloc_page_count + user_objects_dealloc_page_count)*8024)/1024)/1024) as deallocated_mb
from sys.dm_db_task_space_usage
group by session_id
) x where allocated_mb > 0;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 27, 2014 at 7:30 am
Did you perchance change your recovery model from Simple to Full without ensuring that log backups were being taken?
October 27, 2014 at 3:12 pm
Eric M Russell (10/27/2014)
The following can be used to identify what objects are currently consuming space in tempdb (the name of each temp table and it's size) and what sessions are allocating the most space (allocated / deallocated mb). However, for this detail, you must run these while the tempdb space is still actively allocated. The most likely suspects are stored procedures dumping millions of rows into a temp table or a non-indexed hash join between large tables resulting in SQL Server creating temporary hash tables in the background.
I would not combine internal_objects and user_objects page counts, because you will loose a lot of valuable information. By mixing them together you cannot tell any more is it "millions of rows into a temp table" or "hash join" just by looking at the result of the 2nd query you provided. So something like this would make more sense:
-- query tempdb allocation by session:
select session_id, internal_objects_mb, user_objects_mb from
(
select session_id
,sum(internal_objects_alloc_page_count - internal_objects_dealloc_page_count)/128. as internal_objects_mb
,sum(user_objects_alloc_page_count - user_objects_dealloc_page_count)/128. as user_objects_mb
from sys.dm_db_task_space_usage
where session_id >=50
group by session_id
) x where internal_objects_mb + user_objects_mb > 0;
And 8KB is 8192, not 8024.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply