Database space issue

Question

Post reply

Database space issue

ps.

One Orange Chip

Points: 29422
More actions
December 20, 2023 at 1:50 pm

#4349623
Hi experts,
I am facing issue with the database file space used. Here are some details:
Server - Azure database
Data file size - 350 GB
All table combined izes - ~40 GB
Space used within the data file- 340 GB
I looked at sys.allocation units and there are several container_ids that are missing in sys.partitions and hence query used to find actual table size is not showing details for those container_ids. (type for those allocation units is 1)
Can someone plese share some insight as to what those container_ids may be as I am unable to find further details for those?
Regards
- This topic was modified 11 months, 1 week ago by ps..
Pradeep Singh

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24386 More actions · Answer 1

I dont normally work with DB admin.

However, according to the example this page, your joins appear to be incorrect.

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-partitions-transact-sql?view=sql-server-ver16

How to post data/code on a forum to get the best help.

ps. One Orange Chip Points: 29422 More actions · Answer 2

Thanks DesNorton,

Inner join will give me ~40 odd GB used by various table. The intent is to find allocation units that are not part of actual usage hence the left join.. This is the reason that 283 GB is allocated to some ghost objects it seems.

Pradeep Singh

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 3

I would look at the type on sys.allocation_units as well - this may explain if you have a lot of data in large-objects, or in columnstore indexes.

Type of allocation unit:
0 = Dropped
1 = In-row data (all data types, except LOB data types)
2 = Large object (LOB) data (text, ntext, image, xml, large value types, and CLR user-defined types)
3 = Row-overflow data

The documentation states that for type 1 or 3 in a rowstore index then the join needs to be container_id = sys.partitions.hobt_id like you have.

For type 1 or 3 in a columnstore index, container_id = sys.column_store_row_groups.delta_store_hobt_id

For type 2, then container_id = sys.partitions.partition_id

ps. One Orange Chip Points: 29422 More actions · Answer 4

Thanks Kev,

All those have type=1 (in-row data). There is no columnstore index or lob data in the database. Is there a possibility that ADR is not removing allocation units after truncate/delete (one possibility). I don't have a way to check that area though.

Pradeep Singh

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 5

So are you saying the container_id is null in sys.allocation_units?

And that this query gives the same kind of results?

select container_id, sum(total_pages)/128 SizeMB
from sys.allocation_units
group by container_id
order by SizeMB desc

Or that there are values but they don't link through to sys .partitions

DesNorton SSC-Insane Points: 24386 More actions · Answer 6

ps. wrote:

Thanks DesNorton,
Inner join will give me ~40 odd GB used by various table. The intent is to find allocation units that are not part of actual usage hence the left join.. This is the reason that 283 GB is allocated to some ghost objects it seems.

I meant that your joins should be on b.partition_id = a.container_id and not b.hobt_id = a.container_id

How to post data/code on a forum to get the best help.

ps. One Orange Chip Points: 29422 More actions · Answer 7

yeah, that returns the same information.

Pradeep Singh

ps. One Orange Chip Points: 29422 More actions · Answer 8

Interim Update:

MS confirmed it was a most likely a bug most probably introduced around 6th Dec build (azure db) related to dropped rowset clean-up logic heading to dropped rowsets accumulating in the background and consuming space.

We noticed that it happens during index rebuild operation. Our script had the options WAIT_AT_LOW_PRIORITY

( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF) . After removing this clause, we did notice increase in db size but it went down to normal after the operation finished. So this could be the cause as well. Will udpate once we get final answers from MS.

Pradeep Singh