The Incredible Shrinking Database

If you have been on Rock for any length of time, you'll notice as more time passes, your database grows. This can be a good thing! This means you are gathering more and more data and Rock is being used to communicate with people, save attendance logs, facilitate notes, etc... But this comes with it's own set of problems eventually.

The larger your DB is, the slower certain actions can become. Backups, restores, searches, lookups... all can be impeded by a bloated database. What do I mean by bloated? Well... not to sound like an advertisement for the fabulous "Database Thinner" plugin sold by Blue Box Moon, but I love the way they sum it up in their plugin's description on the Rock Shop:

"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."

Website: https://www.blueboxmoon.com/plugin/database-thinner

This plugin can do some very cool things. It has options to compress old communications, remove old emails, delete old check images, find unused files that slip through the cracks (like old profile photos that were since replaced), and on newer versions of SQL or on Azure SQL it can even compress entire tables which can result in faster load times. I do highly recommend it.

Looking at the Box

Normally your Used and Allocated space will be similar (within 10% or so of each other) and that's totally normal and not something you should worry about. But if you've done a major data purge like we have done with Database Thinner, when we log into Azure and navigate to SQL Databases and choose our DB, we see something that looks like this:

Here we see after using Database Thinner on this particular DB, I have 59.97 GB of "Used Space" but my "Allocated Space" is 125.78 GB and my max size for this Azure SQL Plan is 250 GB. Pre-thinning, My "Used Space" was near that 125GB figure. Now it is half of that, but my DB file is still huge when it comes to backups/restores/etc... What's going on? Let's dig deeper.

Looking in the Box

So how can you see how much space something is taking up in your DB? Simple! In your Rock instance, go to "Admin Tools - Power Tools - SQL Command" and paste this code and hit "Run" to near instantly return a table showing all of your DB tables sorted by the largest to the smallest.

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
	CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00 / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceGB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    UsedSpaceKB DESC

Let's digest this image for a second. So we see our largest DB table is "BinaryFileData" taking up 25.14GB of space. This is likely your largest as well. This is where all files that are saved to the DB are kept. Think check images, profile photos, etc... Now you COULD have your File Type set to store those images elsewhere (not in the DB) like to blob storage or to your host server, but by default, this is where it lives. Other large tables would include Communication (every email ever sent through Rock to every recipient), History (all the history items on a user's profile), Interaction... etc.

The next interesting thing are the columns to the right of the "Used Space" columns... the "Unused Space". I know. I can hear you cry out, "What is this??? Why make my DB larger and then not even use the space???" For this, you must change the way you view a database. Think of a DB as a box. Remember the last time you ordered a tiny item from Amazon and it came in an enormous box? What a waste of shipping costs, right?!? Your DB is your box. Your tables are the items in your box. Just like a well packed box, you don't want it wall-to-wall with items and would rather have a little padding in there to prevent shipping damage. In our case, the "Unused Space" is a buffer to allow the DB to grow quickly, without having to resize the entire DB file on the fly with every single change we make. So having SOME "Unused Space" is ideal. For a DB to resize its file is more resource intensive than simply using pre-allocated "Unused Space" already partitioned to accept data.

But even with this "Unused Space" for each table, we don't come anywhere near the ~65GB discrepancy between my "Allocated Space" of 125.78 GB and my "Used Space" of 59.98 that Azure shows. What gives? Well it appears Azure's SQL graph above showing that discrepancy actually counts the Table's "Used Space" and "Unused Space" per table as a combined "Used Space" on the DB. Confused? I hope not. But to say it another way... if you look at the table results and add up all the Used and Unused space, SQL counts that as "Used Space"... and that doesn't account for all of your "Allocated Space" for the entire DB file.

Still not tracking? Let's take the metaphor of an Amazon box further. Each item (table) in the box has it's own packaging (blister packs, cardboard product boxes, foam inserts, etc) which make up their own "Unused Space" next to the actual product they hold which is the "Used Space", but as far as SQL Server is concerned, the combination of the two, product and packaging, ("Used" and "Unused Space" per table) is spoken for and counts as "Used Space" in the box (DB). So now we need to deal with the rest of the overhead... all that extra "Allocated Space" (too large of a shipping box) and bring it down to something reasonable.

Shrinking the Box

So you have deleted/removed/thinned a LOT of items from your DB (say, with the aforementioned Database Thinner plugin) and you log into SQL and see your DB is just as large as before! How can this be? Well, all we've done is removed items from the box. So our "Used Space" decreases, but the overall box (DB file size) doesn't shrink. So while we should have SOME room between "Used Space" and "Allocated Space" we don't want an insane amount. It's a waste of resources (and shipping costs... I'm looking at YOU Amazon).

If you use a plugin like Database Thinner, you may see your BinaryFileData table drop in half (or more)! We took our 16 years of data (yes, 16 years of check images dating back to our Fellowship One days) down to 8 recently and saw just that! Our overall DB went from 125GB to 60GB. That's a MUCH faster backup/restore process. But how did we do this? If we just deleted that data, wouldn't our DB file size stay at 125GB? Yes! But we shrank our box with some magic SQL. "Magic SQL?" you implore... Why yes.

Courtesy of Microsoft: https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-database

DBCC SHRINKDATABASE ("YourDBNameHere", 5);
GO

(replacing DB name, and percent free space to leave for future growth... I went with 5%) This will result in shrinking your DB file wile leaving 5% free space (box padding) for tables to grow without your DB having to expand as soon as your job completes and someone sends an email or updates their info.

BUT! I must warn ye! Answer first, these questions three:

1) Are you in the right program?
To shrink the DB after doing a large compression or data removal/prune, run this as a query in Microsoft SQL Server Management Studio (SSMS). Why SMSS and not in the Rock Power Tool SQL Command? Because I don't find it generally wise to adjust the DB you are actively using from within that platform, and I'm not sure what would happen if you use that to adjust itself... It may work, but why risk it? If you don't have or don't know how to set-up SSMS, check the first 4 steps here where I walk through the process with pictures: https://community.rockrms.com/recipes/259/how-to-maintain-your-azure-sql-database

2) Have you allocated enough time to do this?
Keep in mind this is an ONLINE and LOSSLESS operation, meaning it won't take your server down and you won't lose data if you have to stop it, but it is EXTREMELY resource heavy. It's taking all data and cramming it into every available nook and cranny at the front of the file so the back of the file can be emptied and lopped off. So performance WILL be impacted. In our case, it took a few hours to complete, but your mileage will vary depending on DB size, SQL server compute power, phase of the moon, and whether or not you had a light breakfast that day... and possibly what color your shirt is. So do this when your work won't impact others (i.e. middle of the night).

3) Do you know how to defrag when done?
Lastly and possibly MOST importantly. This will WRECK your indexes and totally fragment your DB (remember how I said it is cramming data into every nook and cranny it can find). So you will need to do some DB maintenance to fix that. No worries. Just keep in mind this can also take multiple hours to complete. I got you covered there too... with my previous link: https://community.rockrms.com/recipes/259/how-to-maintain-your-azure-sql-database

End Result

After you shrink and then do the cleanup, you should be good to go! You'll see something much more akin to your Used and Allocated matching in size. Yes, doing the defrag and letting it rebuild indexes will slightly increase your DB size. That's normal. But it shouldn't be anywhere near what it was before if you did a significant DB shrink.

And if you use the Database Thinner plugin on an Azure SQL database or on a local SQL database (on Standard Edition of SQL Server 2016 SP1 and higher), you can enable compression on select tables. This is relatively transparent to Rock. I say relatively because it DOES require slightly more CPU power to access the data (maybe around 10-20%) but it also can return faster results in certain cases (about 10% as well), and your data takes up significantly less room. In our case it was a solid 20GB of savings on our 66GB of data! That's 30% less size on the DB! So we started this journey at 125GB of space used, and we ended at 45GB. Grab a mirror DB, because you are looking good!