How to get the new database size after deferred drop operations?

Question

We've been using the sys.database_files view and the FILEPROPERTY function to get the size of the database. Unfortunately, as I have just found in the documentation, the values returned by sys.database_files are not always reliable:

When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sys.database_files immediately after dropping or truncating a large object may not reflect the actual disk space available. For more information about deferred allocations, see Dropping and Rebuilding Large Objects.

So, if a large table has just been dropped then when can I query the sys.database_files view reliably? How long shall I wait?

Answer 1

You can use the sys.allocation_units DMV.

Whilst a deferred drop is pending the allocation unit will still be showing with a 0 value in the type column.