SQL Server Space Usage – Different Results from sys.dm_db_file_space_usage with Database Prefix

availability-groupssql serversql-server-2012tempdb

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!

Best Answer

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;

2008R2

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.

2012

Technet explains that in the 2008R2 the DMO only worked on tempdb http://technet.microsoft.com/en-us/library/ms174412(v=sql.105).aspx and in 2012 that restriction was removed http://technet.microsoft.com/en-us/library/ms174412(v=sql.110).aspx.

Hope That Helps.

Related Question