Tip #341: Lean and mean UAT

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:
SQL tables & space occupied

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.

3 thoughts on “Tip #341: Lean and mean UAT

  1. Rocky Sharma says:

    The one thing you need to be careful about is if you are developing managed solutions in your test/dev environment. In this case you don’t want to copy your production data.

  2. Jarrod says:

    SQL has an out of the box report that does this. You can simply select the DB right click and go to reports | Standard Reports | disk usage or disk usage by top tables.

    • Thanks, Jarrod, didn’t know that! Believe it or not, in all my years with SQL Server not even once I ventured past the Reports menu item wrongly assuming that’s not for real sys admins but wusses :D.

Leave a Reply

Your email address will not be published. Required fields are marked *