Inaccurate values for “Currently allocated space” and “Available free space” in the Shrink File dialog for TEMPDB only

  • Article

Last week I went to a customer who showed me the following weird information.

 

He opened SSMS (SQL Server Management Studio) 2008 R2 and connected to one particular instance of SQL Server 2008 R2 in which he observed this behavior we wasn’t able to reproduce with any other.

 

From the Object Explorer window, he expanded the Databases node, then expanded System Databases. Right clicked tempdb, selected Tasks menu option, then Shrink, and finally Files. Such action brought up the following dialog, which as you can see, was already reporting something strange: a negative amount of free space.

2

 

Not only that didn’t fit my customer, he also knew for a fact that the data file wasn’t 8.00 MB but 11.75 MB, because that was what the file system reported as occupied space by that file.

 

3

 

So, it seemed both values were wrong.

 

Customer also mentioned he was only seeing this in that instance of SQL Server and only for tempdb data file. You will see later on why it only occurred with tempdb’s data files.

 

By looking into the source code of this piece of UI, I realized that in order to populate the Currently allocated space text box, it was using the value returned by Size property of the DataFile class (SMO).

Up until the version of SMO that comes with SQL Server 2008 R2, the Size property of an instance of the DataFile class, was being populated with the value returned in the size column of the corresponding row from the master.sys.master_files system table.

 

So I ran the following query:

select name, size, physical_name

  from master.sys.master_files

  where database_id = 2 and file_id = 1

 

and noticed it returned the following results:

name size physical_name
---------- ------ ------------------
tempdev 1024 C:\...\tempdb.mdf

(1 row(s) affected)

 

As per the documentation of sys.master_files, we know size is expressed in 8KB pages. Therefore, the 1024 we obtained as a result corresponds to 1024 pages of 8KB, which results in 8.00 MB. Just what we saw in the UI.

 

However, the following query that used tempdb.sys.database_files instead:

select name, size, physical_name from tempdb.sys.database_files where file_id = 1

 

Reported the actual, most current, data file size:

name size physical_name
---------- ------ ----------------------------------------------------------------------------------------------------
tempdev 1504 C:\...\tempdb.mdf

(1 row(s) affected)

That being 1504 pages of 8KB, resulting in 11.75 MB (or 12032 KB, just what we saw reported by the file system).

But where was that inconsistency coming from in the first place? Why the size reported in master_files didn’t match that reported in database_files?

Well, it happens that when SQL Server autogrows a file which is part of TEMPDB, the size change is not reflected in sys.master_files (or sys.sysaltfiles for that matter). As per the functional specifications of the storage engine, for TEMPDB the change in the size of one of its files is only reflected in those system tables for explicit grows and shrinks, not for those triggered by the automatic mechanisms.

Having said that, it makes the current size information in sys.master_files (sys.sysaltfiles) potentially staled for TEMPDB files.

Starting with SQL Server 2012, what the DataFile class delivers through its Size property is the value the storage engine exposes via the size column of the sys.database_files of the specific database.

 

So, because that value of that table is updated even in the described corner case, and since the code that implements this part of the UI hasn’t changed in 2012 (it still uses the same SMO DataFile.Size property), the information you see from the same dialog when invoked from the SSMS version that comes with SQL Server 2012 is not incorrect anymore.

 

By the way, the reason why the available space (obtained from the AvailableSpace property of the DataFile class) could show negative is because it is the result of substracting the value returned by the SpaceUsed property of the DataFile class (always accurate) to the value returned by the Size property (potentially staled as we’ve seen). If it happens that the amount of used space from your TEMPDB data file is larger than the outdated size reported, you get the negative.

 

The SpaceUsed property is always accurate because its value is whatever returns the SpaceUsed property of the FILEPROPERTY function for that given file. When you invoke that function to find out the space used for one file, the storage engine scans the GAM pages in that file and counts from them how many allocated extents there are. It multiplies that number by 8 (because each extent contains 8 pages), and that is the result you get.

Be aware that since the problem was present in SMO’s DataFile class, not only this dialog of SQL Server Management Studio could be affected, but any other program that relies on those two properties (AvailableSpace and Size) of the DataFile class.