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

sql-server-2005tempdb

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?

Best Answer

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; 
--
Related Topic