Shrinking Your Database to Improve Performance


Location, location, location!

You have probably heard this in real estate, and while this is true in real estate, it is also true in your Microsoft Dynamics GP Databases. The location of your data in your Microsoft Dynamics Databases is key to streamlining your day-to-day activities and bring value to the performance of your company.

While there are processes available within Microsoft Dynamics GP (check links, reconcile, backups...)  which may contribute to healthy and accessible data, there are some additional steps that can be taken to further increase your system performance and improve productivity. One process in particular is shrinking your database. Now wait a second, why would we want to shrink a database? What would be the end goal in doing this?

Shrinking your database is a process we recommend after running an archive using Company Database Archive, especially after archiving a very large amount of data. When large amounts of data is archived from your Production Company Database to your Archive Company Database, some unused space is created, and to further take advantage of this unused space, a shrink of the production company database is recommended. You may ask, how is this unused space created? Well, let’s use some letters referencing them as locations of your data to visualize this better.

Let’s say we have locations (Data)A,B,C,D,E,F in this specific order. You can see that getting from location A, to location D, will be much faster than getting from location A to Location F.

Now let’s say that we moved (archive)Location (Data) C & D, to an archive company database. Following this process, we will now have the following setting in terms of how the data in the database is presented: A,B, Empty Space ,Empty Space , E,F.

We can see that while we no longer have location (Data) C & D - which created some unused (empty) space - we still have Location (Data) F. Furthermore, it will still take the same amount of time for your SQL Database to get from Location (Data) A to Location (Data) F despite the empty space we mentioned above. But it doesn’t have to be this way, because the performance can be improved, and that is where the database shrink process comes in place.

In the same scenario we used above, following the archive, if the shrink database process was used to shrink the database containing the following data (A,B, ,  , E,F), the location (Data)within the database will move from A,B,  ,  , E,F TO A,B,E,F,  , ,  with location (data)  E & F moving closer to A & B. With this shrink process and how the data in the database is now presented as (A,B,E,F,  , ,  ) , as we can see, it will be faster for your SQL database to get from Location (Data) A to Location (Data) F, because location (data) E & F moved closer to A & B, and additionally the empty space (unused space) moved to the end of the file.

In essence, what the shrink process does is moving your available data within your database closer together, so that your existing data is easily available and can be accessed faster. Additionally, this shrink process frees up disk space on your Disk Drive. Finally, it contributes to saving your company money by allowing you to recoup additional disk space instead of buying new disk space.

On enterprise hardware, SQL disk space, often regarded as cheap, overtime ends up being the most expensive aspect, and this is especially true for companies using SSD Drives. From this perspective, buying additional disk space could add up to 10’s of thousands of dollars. Making the right decision in recouping unused space can save your company a significant amount of money.

If you already use CDA and have moved a large amount of data without running the shrink database process, this process will be of great help. Please don’t hesitate to consult with your DBA so you can take advantage of this process. If you have not used CDA however, schedule a Demo now by clicking here and see how your Microsoft Dynamics performance can be increased and your cluttered data can be streamlined for your company’s benefits.

Happy Archiving!

***Please consult with your Database Administrator or your Microsoft Dynamics GP Partner prior to initiate a shrink of your database.

Write a Comment