We have SQL Server 2017 merge replication set up with three subscribers (running SQL Server 2017 Express). We need to clear data from the server database occasionally to reduce the database size at the subscribers before it hits the 10GB limit. We run a script at the server to delete from various tables, and these deletions are replicated to the subscriber tables. The size of the subscriber databases hardly reduces though. Any advice?
Here's a couple of things to be aware of on how SQL Server works:
Your entire database is generally stored in a single
MDFfile (unless you use file groups, then it can be split up across multiple
That file starts out pre-allocating a certain amount of Disk space based on whatever you set it to when you create the database.
As you put more data into your database, once that pre-allocated amount of space fills up, that database file grows to pre-allocate more space (based on whatever you set the Growth settings to).
In either case, this pre-allocated space is managed by SQL Server and is empty internally, despite showing as being consumed on the Disk in the OS, outside of the SQL Server instance.
File growth operations on Disk are expensive.
When you remove data from your database, SQL Server just flips a bunch of bits (for lack of a better description) inside the file to signify that this part of the file can be overwritten. It doesn't actually shrink the file on disk.
This is to minimize the number of expensive file growth operations it needs to do, to improve performance of your database.
This is why when you
TRUNCATE data from a table, it doesn't reduce the file size on Disk. But it does internally mark that part of the database file as applicable for re-use so it can be overwritten when new data comes in.
If you needed to free up space on the Disk and actually reduce the file size, then you can run a
SHRINK operation but typically this is not recommended because it's wasteful I/O and the file will likely grow again as you add more data to your database.
If you're using SQL Server Express edition, where there's a limit to 10 GB per database, that shouldn't be hit until your database file internally reaches 10 GB of consumption, despite the file size on Disk.