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.
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.