Home > MS CRM 4.0 > What takes up most space in an MSCRM database

What takes up most space in an MSCRM database

A frequent question is ‘What is taking up most space in my MSCRM database’. My first step is to check which tables take up most space, for which I use the SQL script at the end of this post. The most common large tables, and reasons for them are:

1. AsyncOperationBase: This stores all asynchronous operations, and can get very large if completed ones aren’t cleared out. You have to explicitly set the registry values AsyncRemoveCompletedWorkflows and AsyncRemoveCompletedJobs. These 2 values are covered in separate KB articles (http://support.microsoft.com/kb/968755 and http://support.microsoft.com/kb/957871). To clear existing records, use the SQL script in http://support.microsoft.com/kb/968520/
2. ActivityMimeAttachment: This stores email attachments
3. AnnotationBase: This stores other files (those associated with Notes)
4. ActivityPointerBase and/or ActivityPartyBase; These store activities and the associated parties respectively. Although individual record sizes aren’t typically large, these tables typically have the largest number of records

For no.s 2 to 4 I’ve not given a way of clearing out space, as this would involve deletion of business data.

SQL Script
I normally use the following SQL Script to find what objects take up most space in a SQL database. It doesn’t take account all possible storage in all SQL databases, but works fine for an MSCRM database

select OBJECT_NAME(object_id), SUM(reserved_page_count) / 128 as ReservedMB, sum(used_page_count) / 128 as UsedMB, MAX(row_count)
from sys.dm_db_partition_stats
group by object_id
order by SUM(reserved_page_count) desc

Categories: MS CRM 4.0
  1. No comments yet.
  1. No trackbacks yet.

Leave a 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: