To make it simple:
According to MSDN (link) among other sources, you can get the amount of current free space in tempdb by using the following bit of TSQL:
-- MSDN version SELECT SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB] FROM sys.dm_db_file_space_usage; -- The "other" version SELECT SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB] FROM tempdb.sys.dm_db_file_space_usage;
Now, we're using a scheduled script that dynamically does what it can without going overboard with space usage. Prior to today, the MSDN version worked fine in environments using clustered SQL Server 2008's, but today I found out that one of our new environments with SQL Server 2012 and AlwaysOn Availability Groups behaves differently.
There, the "MSDN version" returns almost no free space at all, while the "other" version correctly shows the available free space in tempdb.
Can someone explain this to me? Is there a shared tempdb among all the AlwaysOn replica groups so that by default, not specifically defining tempdb somehow defaults to some local version of tempdb, or what is really going on here? Can I trust that the queries run against the db actually utilize tempdb.sys.dm_db_file_usage_space as opposed to whatever it is that regular sys.dm_db_file_usage_space is pointing out to?
I'm trying to find out more about this myself, but we need an answer quite urgently, so I'm asking here just to be sure. Thanks!