Rock 1.15.0 Documentation

Database Thinner

Rock is an amazing system that tracks a huge number of data points. This is great news for you as it means down the road when you want to build your reports, all the data is available and ready. However, it also means it tracks and records lots of historical data that you may not need. The reason for this is that Rock doesn't know what you want and what you don't want. You might only need to keep copies of your scanned checks for 2 years, but another church might have a requirement to keep them for 5 years. Because it doesn't know the data retention requirements of every church, it keeps all this historical data around forever.

The Database Thinner plugin will help you find and delete much of this historical data that you don't need anymore.

Old communications can have their contents compressed so that they only use 10% of the original space. Why doesn't Rock do this out of the box? Well, what if you want to go back and look at the details of a communication you sent last week? Or last month? Or maybe last year for the same event? If the data is compressed, you can't look at it. This plugin will let you pick how long to keep communication data uncompressed. Once that period of time passes it will be compressed for you. But don't worry, a simple click of the mouse while viewing the communication will bring it back to it's original state so you can view it again.

Did you know that Rock keeps a copy of nearly all e-mails it sends? Every birthday e-mail. Every password reminder. And many more. Do you really need to keep a history of the fact that you sent 27,339 birthday e-mails three years ago? Those couldn't take up that much space, right? What if you learned that each of those e-mails might be taking up 6.7KB? That still isn't much. Except it totals up to 185MB. Per year. Do you really want your database to grow by 185MB per year just because of the birthday e-mails you send out?

How many checks do you scan each week? Sure each check only takes up about 23.4KB in your database. But that adds up quickly. Do you scan 100 per week? 250 maybe? 1,000 if your a large church is not unlikely. If you are only scanning 250 checks per week, that adds up to 304MB per year. How long do you need to keep those around? Your finance department can probably help you determine the retention period, but more than likely you probably don't need to keep them for more than two years.

Rock does the best job it can of deleting files. But there are many cases when it just doesn't know if it is safe to actually remove that file from the database. Because of this, there are times that files are left in your database even if they aren't actually used anymore. We can scan for anything that references these files and make a list of files that are almost certainly not used anymore. Because we are not 100% sure, by default we will quarantine these "orphaned" files for 90 days. This way, if somebody calls you and says a link on the website or something isn't working anymore and you find it's a link to one of these orphaned files, you can restore it back in place.

Did you know, if you are running on Azure SQL or SQL Server 2016 SP1 or later, that SQL Server can compress the database tables for you? This is completely transparent to Rock. There are various limitations to this functionality (which is why we can't just use this method to compress your Communication data), but most tables can see significant savings. Additionally, some studies have shown that SQL performance actually increases by compressing data. The reason for this is that it has to read less data and with the processing power of today's CPUs, decompressing data is faster than waiting for a SAN to return data from the spinning disks. Many tables can be compressed to 40-50% of their original size.


Version History

Documentation

New Features

  • Added configurable database timeout option to automation job.
  • Added Storage Provider to Unused Files grid to better identify the file.

Bug Fixes

  • Fixed unused file removal to not throw an error if the remote file doesn't exist anymore.
Documentation

Bug Fixes

  • Fixed an issue that could cause the automation job to timeout while deleting system communications.
Documentation

Bug Fixes

  • Fixed default permissions on Decompress Communication page.
Documentation

Initial Release

  • See statistics about how fast your database is growing.
  • Compress Communication content at up to 10:1 ratio for archived communications.
  • Delete old system communications (such as birthday e-mails) that are not needed.
  • Recommends and compresses tables that are good candidates for table-level compression (requires Azure SQL or SQL Server 2016 SP1 or later).
  • Delete old transaction images if you are only required to keep them for a certain number of days.
  • Find and delete unused binary files from the database.