65G SQL Server database has 5.5G of data in tempdb when not in use I have a SQL Server instance with 1 production database (also ReportServer dbs) that is 65G.  tempdb is 5.5G (well, actually much bigger but 5.5G is in use) when server is not in use.  I believe this is not normal.  How do I diagnose what is causing this space to be filled and not released?
Non-temporary user objects

Maybe there are non-temporary user tables in tempdb.  These wouldn't get cleaned up automatically (unless the server is restarted).  You can check for them like this:

USE tempdb;
GO
SELECT * FROM sys.objects WHERE [type] = 'U';


To release this space, you'll need to manually drop these tables using DROP TABLE statements.  Make sure no one is still using them first 

Sleeping sessions keeping temp objects alive

Another possibility is that someone created a temp table, and their session is still active.  I created a temp table using the Stack Overflow sample database and left the session open:

SELECT * INTO #Users FROM dbo.Users;


Checked space usage:



All 4 tempdb files are using about 15 MB of space.

You can see that the session is still open by checking sp_WhoIsActive with the option to show sleeping spids:

EXEC master.dbo.sp_WhoIsActive @show_sleeping_spids = 2;




Look for sessions that have tempdb allocations, or otherwise suspicious sessions that have been open for a long time.

If I close the window for session 53 in SSMS, and then re-check sp_WhoIsActive, the query is gone.  And now I check tempdb space usage again:



You can see that "Space Used" dropped in all 4 files.

To release space that's being held like this, you'll need to either:


track down the person in the login_name field and ask them to end their session, or
kill the session using the KILL command