On the table and index organization page in the sql server documentation link here, under the Allocation Units section, the documentation states:
Each table that has at least one LOB column also has a row in sys.partitions with index_id > 250.
Whilst looking for sparsely populated image columns in one of my tables that’s taking up waay too much room I noticed that said table does indeed contain a LOB column (image data type), yet has no index listed in sys.partitions with an index_id > 250.
Here is the results from querying the sys.partitions table:
Here’s the allocations from the sys.system_internals_allocation_units table. Obviously a bunch of unused space in this table.
Is the documentation wrong? Why you sys.partitions no have index_id > 250 for image column?
5 thoughts on “LOB Columns & sys.partitions”
whats the version of SQL and compatibility level of the database where this table is? maybe because “image” is to be deprecated, you may have fallen into a bug due to some weird versions conflict.
The version of sql is Sql 2008 R2 EE RTM (must patch!). The compatibility level of the database is 10.50.1600.1. I’ll patch the sucker this weekend and test it again.
GL and post here if it works!
Did it work??? 😀
Sorry, haven’t tried yet. I wasn’t able to take the server down early for maintenance. I will be 100% sure to post if it did though. Won’t be for a couple weeks yet though, vacation! Wooooo!!!