Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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!

share|improve this question
Where is "there"? On an Availability Group's primary, or on a secondary? And you're talking about running these queries at the exact same time, right? From here, I see no difference in either version. No matter where you call the view from, the definition is still SELECT * FROM OpenRowset(TABLE DM_DB_FILE_SPACE_USAGE) - now, I don't know the internals of that TVF, but I can't envision how it would work differently without the tempdb prefix. – Aaron Bertrand Jan 30 '14 at 15:00
Well honestly, the whole alwaysOn availability group matter is new to me. But if I log on to both groups directly and use the same queries, they still both return the same miniscule amount of free space without the tempdb prefix, while with the prefix they both return the correct amount. The only difference is the primary group has slightly less tempdb space free with the prefix than the secondary, which I assume is expected due to some lag from transferring data between the two. But as I said, I'm seriously new to this, so I've no idea myself what's going on. Yet. :) – Kahn Jan 30 '14 at 15:19
What is the connection string you're using? Perhaps your queries against the availability group database are not running on the server you think they are. – Aaron Bertrand Jan 30 '14 at 15:20
What could be different about the connection string, specifically? I've tried going to both servers directly as well as through the controlling server which automatically points to the active primary group, using windows authentication. In both cases @@SERVERNAME returns the same value and the tempdb queries behave the same way. – Kahn Jan 30 '14 at 15:25
up vote 1 down vote accepted

It looks like that particular DMO was changed to work with all databases. This can be demonstrated by running the following scripts on a 2008r2 server.

SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM model.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;


As you can see the output will be the same. However, if you run the same scripts on a 2012 server the output will be different.


Technet explains that in the 2008R2 the DMO only worked on tempdb and in 2012 that restriction was removed

Hope That Helps.

share|improve this answer
That does seem to be the only explanation, and you confirmed the results otherwise too on your end. Thanks a bunch! – Kahn Jan 31 '14 at 6:33

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.