August 5, 2015
Back in 2013 I published 5 posts about the Secrets of the Metaverse:
Parts 1-5:
- What is the Metaverse?
- How is the Metaverse data stored?
- Is there a limit to how many Metaverse attributes I can have?
- Has access to the metaverse gotten faster with recent releases?
- How do I safely query the metaverse?
- Added (Aug 5 2015): How Many Metaverse Attributes can I have?
The third post was about how many attributes you can have in the Metaverse in which I said that the mms_metaverse_lineageguid table limits us to 502 single valued non-reference attributes in the Metaverse. This is still correct but a client told me of a scenario they encountered where the lineageguid table prevented them from getting to over 450 attributes and they encouraged me to blog about how they solved it.
The issue can occur when you delete attributes from the Metaverse and then try to add more. If you exceed 502 single valued non-reference attributes that have ever existed in your Metaverse you will encounter this error unless you take some very specific actions at the database level. WARNING: these actions should be done under the direction of Microsoft Support so that your installation can remain in a supported state.
The client had deleted a number of unused attributes prior to adding the many attributes they needed and then hit a brick wall getting the following error in their application event log:
0x8023042e (the table cannot be created because the row size limit was exceeded.):SQL: ALTER TABLE [dbo].[mms_metaverse_lineageguid] ADD [theirAttribute] [uniqueidentifier] NULL 0x80230629 (the specified metaverse schema has too many attributes).
First: Why does this happen?
As you can see from the error message when you add or delete a single valued non-reference attribute to the Metaverse the Synchronization Service runs an ALTER TABLE statement to add or delete a column and as famed SQL MVP and author Kalen Delaney states running “ALTER TABLE will not reclaim space.” Her article is about altering the length of column but “Database Whisperer” Michael J Swart provides an example of removing columns and shows that ALTER TABLE just makes a meta data level change. So even though the column is not used anymore it is still taking up space in the table until the Clustered Index is rebuilt.
You can see how close you are getting by using the following query (I used Michael’s and Kalen’s queries as starting points):
USE FIMSynchronization
GO
SELECT c.name AS [Column Name], column_id, leaf_offset, pc.is_dropped
FROM sys.system_internals_partition_columns pc
JOIN sys.partitions p
ON p.partition_id = pc.partition_id
LEFT JOIN sys.columns c
ON column_id = partition_column_id
AND c.object_id = p.object_id
WHERE p.object_id=object_id(‘mms_metaverse_lineageguid’)
ORDER BY pc.leaf_offset
GO
Column Name Column_ID Leaf_Offset Is_Dropped
-—————– ————- ——— ————–
title 99 1564 0
type 100 1580 0
uid 101 1596 0
NULL NULL 1612 1
NULL NULL 1628 1
testAttribute3 105 1644 0
In this example I have added three attributes and then deleted two of them (the first two I added). As you can see this leaves behind some open space in the mms_metaverse_LineageGuidtable and means that I would hit the limit sooner than I would expect.
If the biggest Leaf_Offset is 8044 then you are out of space to add more single value non-reference attributes (8044 byte offset+16 bytes =8060 bytes limit for a SQL row).
Normally, rebuilding the clustered index will reclaim the space for you. So you think no problem since you have followed my advice in FIM Best Practices Volume 1 and you use Ola Hallengren’s scripts to automate index maintenance. However, the script will only rebuild if the index is more than 30% fragmented otherwise it will just reorganize it (which doesn’t reclaim the space). So you could rebuild the clustered index by hand. Oops! The mms_metaverse_LineageGuid table doesn’t have a clustered index – so you have to add one. But then to return the database schema to its supported state you need to drop the clustered index. You can the clustered index on the object_ID column as this will be unique and not null. Then drop it.
ONCE AGAIN: ONLY DO THIS UNDER THE DIRECTION OF MICROSOFT SUPPORT if you want to stay supported (and with all Syncs halted).
CREATE CLUSTERED INDEX [CX_mms_metaverse_lineageguid_object_id] ON dbo.mms_metaverse_lineageguid ( object_id)
GO
DROP INDEX [CX_mms_metaverse_lineageguid_object_id] ON dbo.mms_metaverse_lineageguid
The creating or dropping of a clustered index forces the rebuilding of any non-clustered indexes. Unfortunately that means that this happens twice. But that can not be avoided. For a big table that can take a while (tens of minutes for metaverse with hundreds of thousands of rows). Of course the pure DBA would prefer to create an appropriate Cluster Index and leave it, rather than drop it, but that would put me out of support by the MIM product group.
When I rerun the query I get:
Column Name Column_ID Leaf_Offset Is_Dropped
-—————– ————- ——— ————–
title 99 1564 0
type 100 1580 0
uid 101 1596 0
testAttribute3 105 1612 0
You can see that testAttribute3 is now offset at 1612 and the other columns are gone. Now that space is available to use for the lineageguids of attributes.
So that is how you reclaim the space so you can add more attributes.
http://feeds.feedburner.com/IdentityLifecycleManagerilmBestPractices