User Table In tempdb?

I am getting low disk space on production database server with SQL Server 2008 R2. The tempdb size has increased to 51G. It contains static tables, I mean when I look at tables in tempdb it has multiple number of tables. Most of these tables had 0 records but in two of the tables

select COUNT(*) from t102523_2E2CF266AB2F457E888427A000F5D2F3; --820310

select COUNT(*) from t102523_BB4717747002489CBD001E91669C3967; --1051323

I want to know why and on which scenario these tables gets created.I know if I restart the server disk space will get released. I also want to track on which scenario or action this has occurred. This issue has become normal once in a month.

Answer

We can’t tell you how those tables got created. You’ll need to check the code of any 3rd party or other applications you’re running, or ask your fellow developers / DBAs if they created these specific objects for a reason. SQL Server does not just automatically create user tables in tempdb unless you tell it to.

You may be able to find who created these tables in the default trace, if they were created recently enough:

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT ObjectName, HostName, ApplicationName, LoginName, StartTime
FROM sys.fn_trace_gettable(@path, DEFAULT) AS t
WHERE DatabaseName = N'tempdb'
AND ObjectName LIKE N't102523[_]%'
AND EventClass = 46 AND EventSubClass = 0
--AND EXISTS (SELECT 1 FROM tempdb.sys.tables WHERE name = t.ObjectName)
ORDER BY StartTime DESC;

No luck? Try reading the log directly.

Still no luck? You may want to check if any user tables exist in the model database, and chase those culprits down, because anything you create in model will end up in tempdb as well on subsequent restarts.

If you can’t locate the cause, you could run a server-side trace (not actively run a trace in Profiler), capturing object:created and filtering to tempdb and where name not like '#%';. You could also consider Extended Events, DDL triggers, Audit, etc.