One of the most wildly accepted ways to refresh your test database is to copy/redeploy production database back into the test environment. (If you don’t have a test database, go and receive your Spießrutenlaufen, then come back and create the database; if you can still move, that is).

Common issue with this approach, however, is the size of the production database. Blind copy will take unnecessary (and expensive in case of CRM Online) space that is simply wasted. Unless there is a specific requirement to test some obscure functionality, there is no reason to cling to those email attachments and notes files.

Find the offending entities

The following SQL script will display database tables in the descending order by space occupied.

declare  @tmpTableSizes as table 
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)
insert @tmpTableSizes
EXEC sp_MSforeachtable 
   @command1="EXEC sp_spaceused '?'"

select  * from @tmpTableSizes order by 
   cast(LEFT(reservedSize, LEN(reservedSize) - 4) 
      as int) desc

It’ll produce something like this:

Delete unwanted records

Now that you identified the offending entities, take approach documented in the administrator’s guide and use it as a baseline. For example, you’d want to expand definitions of the emails with attachments (method 4) to cover not only Completed status reason but also Sent and Received. And don’t hesitate to specify different time conditions or maximum attachment size to reduce database size even further.

This approach will work equally well on-premises as well as in CRM Online. To get the space occupied by your data per table/entity basis, simply raise a support request – and you’ll have this information for your CRM organization in no time.