Why did my tempdb fill up, and what was trying to shrink it?

Question

After months of perfectly flat disk usage, my tempdb file suddenly grew by several gigs over the weekend. Nobody at the company is aware of anything that might have changed.

When I checked the tempdb database, it had only a few very small tables, whose names were strings of hex digits.

In searching for the cause, I found the following message repeated every few minutes for several days in the event log:

DBCC SHRINKDATABASE for database ID 2 is waiting for the snapshot transaction 
with timestamp 51743762409 and other snapshot transactions linked to timestamp 
51743762409 or with timestamps older than 51801253540 to finish.

I can't find any possible way that DBCC SHRINKDATABASE could have been run by anybody on the tempdb (which is DB ID 2). Microsoft's own documentation says that SHRINKDATABASE should never be run on tempdb while it's online, so I can't imagine that SQL server is running it itself.

I'm trying to figure out:

What could have caused such sudden rapid growth in the tempdb file? I'm not aware of any code that uses temporary tables or declares table variables on this server. What else uses the tempdb file?
Why is DBCC SHRINKDATABASE running on tempdb at all, and why is it failing?

score 1 · Accepted Answer · answered Apr 12 '11 at 16:31

First I would check the default trace if someone is manually running the DBCC SHRINKDATABASE command. The following code will help you as DBCC stmt is audited in the default trace. can you share your SELECT @@VERSION?

DECLARE @filename VARCHAR(255) 
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

SELECT gt.HostName, 
       gt.ApplicationName, 
       gt.NTUserName, 
       gt.NTDomainName, 
       gt.LoginName, 
       gt.SPID, 
       gt.EventClass, 
       te.Name AS EventName,
       gt.EventSubClass,      
       gt.TEXTData, 
       gt.StartTime, 
       gt.EndTime, 
       gt.ObjectName, 
       gt.DatabaseName, 
       gt.FileName, 
       gt.IsSystem
FROM [fn_trace_gettable](@filename, DEFAULT) gt 
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
WHERE EventClass in (116) --AND gt.EventSubClass = 2
ORDER BY StartTime DESC;

The below will give you if the data and log files grew recently and can help identify why?

DECLARE @filename VARCHAR(255) 
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

--Check if the data and log files auto-growed. Look for tempdb, log files etc.
SELECT 
    gt.ServerName
    , gt.DatabaseName
    , gt.TextData
    , gt.StartTime
    , gt.Success
    , gt.HostName
    , gt.NTUserName
    , gt.NTDomainName
    , gt.ApplicationName
    , gt.LoginName
FROM [fn_trace_gettable](@filename, DEFAULT) gt 
JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id 
WHERE EventClass in ( 92, 93 ) --'Data File Auto Grow', 'Log File Auto Grow'
ORDER BY StartTime; 
--

I should learn to read the tags correctly. Looks like you have already mentioned that you are using 2005 but it helps to know the output of SELECT @@VERSION, which gives the edition and the exact build you are using. — Sankar Reddy, Apr 12 '11 at 16:36
There were no maintenance plans, but a bit of digging after running your query confirmed that there was a script trying to shrink it. What was causing it to grow was unrelated: A process was starting transactions in a busy DB but never committing them, so all further writes to that DB were using the version store, causing tempdb to grow even though there were no temporary objects explicitly created. — Josh, May 20 '11 at 17:54

score 0 · Answer 2 · answered Apr 12 '11 at 16:26

0

Check your maintenance plans. Someone may have manually altered or added one to the server. Also check if the recovery mode of your database was recently changed.

answered Apr 12 '11 at 16:26

Joel Coel

12,910
13
61
99

Joel, Unfortunately the recovery model of the tempdb can't be changed. Good point about Maintenance plans and my queries below will help identify those. – Sankar Reddy Apr 12 '11 at 16:32

Why did my tempdb fill up, and what was trying to shrink it?

2 Answers2