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.

image

Here is the results from querying the sys.partitions table:

image

Here’s the allocations from the sys.system_internals_allocation_units table.  Obviously a bunch of unused space in this table.

image

Is the documentation wrong?  Why you sys.partitions no have index_id > 250 for image column?

Y U No - sys.partitions why u no idx gt 250?

5 thoughts on “LOB Columns & sys.partitions

  1. 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.

    1. 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.

  2. 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!!!

Leave a Reply to mauriciorpp Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.