When looking at a database, this query:

SELECT * FROM sys.allocation_units a WHERE (a.total_pages - a.used_pages) < 0

surprisingly returns a row. This is the result:

| allocation_unit_id | type |  type_desc  |   container_id    | data_space_id | total_pages | used_pages | data_pages |
|  72057594058702848 |    1 | IN_ROW_DATA | 72057594047692800 |             1 |         105 |        111 |         93 |

My understanding is that pages are 8kb groupings of space on the disk (Is this correct?)

How can I have more used_pages than I have total_pages?

  • Is that a HEAP? – Erik Darling Apr 21 '17 at 21:02
  • @sp_BlitzErik, I get similar results on clustered tables. – John Eisbrener Apr 21 '17 at 21:18
  • Can you find an example where the difference is bigger than 8 (ie. one mixed extent size) ? – Remus Rusanu Apr 22 '17 at 7:13
  • And also, does the difference persists after running DBCC UPDATEUSAGE? – Remus Rusanu Apr 22 '17 at 13:19
  • @RemusRusanu: When I run UPDATEUSAGE on that database, the query above then returns no rows – Zach Smith Apr 24 '17 at 14:11

I suspect it is related to the fact that this DMV is updated asynchronously behind the scenes. Per the sys.allocation_units BOL page:

...the values returned by sys.allocation_units immediately after dropping or truncating a large object may not reflect the actual disk space available

While this implies only drops and/or truncates won't be tracked in real-time, I just ran the same query against one of my environments where nothing is ever dropped or deleted and am getting similar behavior to your results. I suspect this view just is updated in the background and whatever it is that has to trigger to spawn the update hasn't happened yet.

Hopefully someone else has a better answer as I'd be interested to know for certain as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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