Inaccurate values for ' currently allocated space ' and ' Available free space ' in the Shrink File dialog for TEMPDB only

Reprinted from: http://blogs.msdn.com/b/ialonso/archive/2012/10/08/ Inaccurate-values-for-currently-allocated-space-and-available-free-space-in-the-shrink-file-dialog-for-tempdb-only.aspx

Inaccurate values for ' currently allocated space ' and ' Available free space ' in the Shrink File dialog for TEMPDB only

Rate this

Nacho Alonso Portillo

8 Oct 4:47 AM

• 2

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

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

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

Didn ' t fit my customer, he also knew for a fact, the data file wasn ' t 8.00 mb but 11.75 MB, because that Was, the file system reported as occupied space by. File.

So, it seemed both values were wrong.

The Customer also mentioned he is only seeing the this in, instance of SQL Server and only for tempdb data file. You'll see later for why it's only occurred with tempdb's data files.

By looking into the source code of this piece of UI, I realized so in order to populate the currently allocated SPAC e text box, it is using the value returned by Size property of the DataFile class (SMO).

Up until the version of SMO this comes with SQL Server R2, the Size of a instance of the DataFile class, WA S being populated with the value returned in the Size column of the corresponding row from the Master.sys.master_files sys TEM 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 1024x768 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 1024x768 we obtained as a result corresponds to more than pages of 8KB, which results in 8.00 MB. Just What do 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's being 1504 pages of 8KB, resulting in 11.75 MB (or 12032 KB, just what we saw reported by the file system).

But where is the inconsistency coming from in the first place? Why the size reported in Master_files didn ' t match this reported in Database_files?

Well, it happens this when SQL Server autogrows a file which are part of TEMPDB, the size of change is not reflected in sys.ma Ster_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 are only Reflected in those system tables for explicit grows and shrinks, not for those triggered by the automatic mechanisms.

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

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

So, because that value of the, table was updated even in the described corner case, and since the code that implements this Part of the UI hasn ' t changed on (it still uses the same SMO Datafile.size property), the information He same dialog when invoked from the SSMS version, comes with SQL Server, not incorrect anymore.

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

The SpaceUsed property was always accurate because its value was whatever returns the SpaceUsed property of Thefileproperty function for that given file. When you invoke this 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 is. It multiplies that number by 8 (because each extent contains 8 pages), and that's the result you get.

Be aware that since the problem is present in SMO's DataFile class, not only this dialog of SQL Server Management Studio Could is affected, but no other program that relies on those the properties (AvailableSpace and Size) of the datafile CLA Ss.

Inaccurate values for ' currently allocated space ' and ' Available free space ' in the Shrink File dialog for TEMPDB only