Checking the size of your CRM tables

I'm sure this is posted a few other places, so I definitely cannot take the credit for writing this script, but this is a great way to see what entities in CRM are taking up the most space. If space ever becomes an issue, or you're just looking to clean your data a bit and make querying your data a little faster (due to less data to be sifted through), this is a good way to find where you should focus your attention.

Run this SQL script against the Organization data base (OrgName_MSCRM):

SELECT
sys.schemas.[name] AS [Schema],
sys.tables.name AS [Table],
COALESCE([Row Count].[Count], 0) AS [Rows],COALESCE(8192 * [Data Pages].[Count],0) AS [Data Bytes],
COALESCE(8192 * [Index Pages].[Count],0) AS [Index Bytes]
FROM sys.tables
INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id
LEFT OUTER JOIN (SELECTobject_id,
SUM(rows) AS [Count]
FROM sys.partitions
WHERE index_id < 2
GROUP BY object_id) AS [Row Count] ON [Row Count].object_id = sys.tables.object_id
LEFT OUTER JOIN (SELECTsys.indexes.object_id,
SUM(CASE WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0 END) AS [Count]
FROM sys.indexes
INNER JOIN sys.partitions AS p
ON p.object_id = sys.indexes.object_id
AND p.index_id = sys.indexes.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY sys.indexes.object_id) AS [Data Pages]
ON [Data Pages].object_id = sys.tables.object_id
LEFT OUTER JOIN (SELECTsys.indexes.object_id,
SUM(a.used_pages - CASE
WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0 END) AS [Count]
FROM sys.indexes
INNER JOIN sys.partitions AS p
ON p.object_id = sys.indexes.object_id
AND p.index_id = sys.indexes.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY sys.indexes.object_id) AS [Index Pages]
ON [Index Pages].object_id = sys.tables.object_id
ORDER BY sys.tables.[name]

Hope this comes in handy to someone else.

David Fronk
Dynamic Methods Inc.

SiteMap Privilege Tag

At times there are users who need to read data from entities but those same users should never really see the full list of items of that entity in a place where they could take action against any of the items.

For example, an entity called "Locations" exists in CRM. Users will need to have the ability to view these Locations in order to enter a Location on a related object form, perhaps an Account. As an administrator you would like to have the list available to you so that you can add to or modify the list for the users. It could be placed in the Settings area and sometimes that is enough to keep people away from the list. But just to be safe you want to guarantee that users cannot do anything to the list.

This is where SiteMap Privilege tags come in. Within each SubArea tag a Privilege tag can be added. When a Privilege tag is applied, CRM will check on the main page load what privileges the user has to see if that user should be able to see the item. If the user does not have rights, then the item is not shown, if the user does have rights then the item is shown.

So, following our example from above the following could be inside the SiteMap:

<SubArea Id="new_location " Entity="new_location">
<Privilege Entity="new_location" Privilege="Write" />
</SubArea>

By setting the privilege to "Write" only those users that have the write privilege will be able to view the entity from the main CRM page. Multiple privileges may be used as well. Here are the possible values from the SDK:

All
AllowQuickCampaign
Append
AppendTo
Assign
Create
Delete
Read
Share
Write

Here's an example with multiple privileges:

<SubArea Id="new_location " Entity="new_location">
<Privilege Entity="new_location" Privilege="Read,Write,Share" />
</SubArea>

And finally, get creative. Just because the area is for an entity doesn't mean that the privilege has to be for the same entity. Perhaps the Location entity should only show up to users who have rights to write to the Knowledge Base. The following would be completely legit as well:

<SubArea Id="new_location " Entity="new_location">
<Privilege Entity="kbarticle" Privilege="Read,Write" />
</SubArea>

Now your main CRM page can be much more dynamic depending on who you are.

David Fronk
Dynamic Methods Inc.

Managing the AsyncOperationBase table entries

Since Update Rollup 3 a new function has been available but most likely not very widely used. That is the function of AsyncRemoveCompletedWorkflows. There is a Microsoft KB article on this that you can read here. But this function makes it so that as soon as a workflow (asynchronous job) is completed it is then marked for deletion.

This hotfix is proactive only. Therefore, when the hotfix is installed and activated, only new workflow records are deleted automatically. If the AsyncOperationBase and WorkflowLogBase tables are already large because of these workflow records, you must perform a cleanup of these tables by using the Microsoft SQL Server cleanup script that is included in the following Microsoft Knowledge Base article:
968520 (http://support.microsoft.com/kb/968520/ ) Performance is slow if the AsyncOperationBase table becomes too large in Microsoft Dynamics CRM 4.0 (from KB article 968755).

I have had some clients not manage this table very well and it made their system a bit slower or just waste data space. One client had over 3 million rows in this table and their Company_MSCRM database was over 15GB. I cleaned out the table and brought the database size down to 1.4GB. That's a lot of space being wasted. If you want the logs, then it's not wasted space but I doubt that every row of that table needs to be saved, so figure out what workflow types you need and keep those and clear out the rest.

Another great blog regarding this issue can be found here. Ben goes into a lot more depth and really talks through some great ways to manage your DB, especially the AsyncOperationBase table.

Hope this is helpful to others.

David Fronk
Dynamic Methods Inc.

Scribe MVP

Scribe Software has just announced their MVP program and honored me with being invited and accepted into their MVP community.

Scribe Most Valuable Professionals represent the most experienced and skilled Scribe users from among our over 800 partners, 10,000 customers and related software vendors. Scribe MVP’s are invited to join the program based on their skills, experience and contribution to the Scribe OpenMind community. For details on the Scribe MVP program click here.

As a note, the current 13 MVP's (as far as I am aware) were all invited/nominated by Scribe employees and the technical team. Don't quote me on that but since they state in their "How to become an MVP" document that you are either "nominated by someone from Scribe, someone else, or yourself", no one other than Scribe knew this was coming. Meaning the invites could only have come from the people within their company. Which speaks to the expertise and involvement of these initial MVP's with Scribe's software and the team behind it. To see the full listing of Scribe MVP's click here.

And if you are unfamiliar with Scribe's services you should definitely check out their website. For data imports, data movement, and system integrations Scribe has a very robust platform and capability to connect and push/pull data from almost any data source. Their library of application adapters is growing and they have found a good niche in the Microsoft Dynamics arena. Did anyone else notice that all of the MVP's are from the Microsoft Dynamics CRM realm? Microsoft CRM typically takes companies from an old CRM system, or Excel Sheets, Outlook contacts, or any other random place that people have decided to track information regarding customers or something that makes up the X in their new XRM solution. Scribe bridges that gap by giving visibility to data and mapping data with custom logic so that the data can be imported correctly and even have some clean up done on the way into the new system.

My congradulations to all of the other Scribe MVP's and thanks to Scribe and their team for this honor.

David Fronk
Dynamic Methods Inc.