Friday, September 28, 2007

Determine space available for custom attributes

For anyone who has read the Microsoft CRM Customizations book this SQL script may not be anything new but I saw this script and thought it was really cool. I actually have a client who cannot add any more "bit" attributes to the Contact entity so when I saw this SQL Script I was really excited to see what it told me about the space available on their Contact entity.

Here's the script:

--Run this on the metabase
--space remaining in table
select e.Name, Byte_Remaining = 8060-(sum(a.length))
from entity e join Attribute a on e.entityid = a.entityid
where a.iscustomfield = 1
and a.islogical = 0
group by e.Name
 
--script to see current size of tables, can't go over 8060
select e.Name, Physical_size = sum(a.length)
from entity e join Attribute a on e.entityid = a.entityid
where a.iscustomfield = 1
and a.islogical = 0
group by e.Name

You don't run out of space very often but if you're afraid of running out then this script may help you out. For my client that cannot add any more "bit" attributes, this script showed that they still had 7000+ bytes available on the table. So, apparently there may be some limit as to the number of attribute types. I haven't found a solution for that piece yet but hopefully I will soon. If anyone has seen this and has figured out a way around the problem I would love to hear it.

I hope this script helps someone out (whether you've read the MSCRM Customizations book or not).

David Fronk
Dynamic Methods Inc.

2 comments:

Michael Dodd said...

That's some fantastic SQL right there. Thanks for sharing. Leads and Contacts are infamous for blowing out this limit, it's good to have a check in place for CRM Customizers. Have you considered plugging this into a CRM report? Your parameter would point to all available customizable ExtensionBase tables....

Dynamic Methods said...

Thanks. I haven't considered plugging this into a CRM report yet, but that's not a bad idea. I may just have to try that out.

Post a Comment