How to determine size of specific tables in a content database (4277198)

The content database has been shrunk following an externalization, but still appears to be larger than expected.

There is often a subset of table objects containing large amount of data such as audit tables, which a user is previously unaware of. It may also be that the amount of data intended to be externalized has not been externalized due to unintended filters being applied.

Within a query editor scoped to the content database to be analyzed, execute the SQL procedure below, which will dump the space utilization for each table in the database.

sp_msforeachtable 'exec sp_spaceused [?]'

NOTE: The reserved column is the total space (data + index) used.

Below are some explanations if the following tables are large in size:

• AllUserData - Metadata is consuming the space.
• AuditData - Audit data may need to be cleaned up or disabled.
• AllDocStreams (or DocStreams in SharePoint 2013 or later) - There may still be a large amount of content that is not externalized. Review the externalization filter settings for the profile and determine if they need to be amended and if the externalization job has to be run again.