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?
2
-
You're using SQL Server Express Edition?– J.D.Nov 16, 2022 at 17:51
-
Only on the subscribers– Steve ColleyNov 18, 2022 at 8:42
Add a comment
|
1 Answer
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 multipleNDFfiles).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 DELETE or 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.
-
-
We have run a shrink on the subscriber databases but that did not free up much physical disk space Is there a query we can run to report the used database size (as opposed to the physical space)? Nov 18, 2022 at 11:09
-
Ok so I used EXEC sp_spaceused @updateusage = N'TRUE'; on one of the subscriber's database. This gives me (can't post the result image) database_name database_size unallocated_space xxxxxxxxxxxxxxx 8967.63 MB 47.23 MB reserved data index_size unused 8085904 KB 4519640 KB 3226576 KB 339688 KB The delete script we run has removed roughly 75% of the data but this is not being reflected above We would in the past have disconnected replication, run delete script, then rebuild replication. Nov 18, 2022 at 12:57
-
That would give us a database size as expected. In this case we had to run the script while replication is active. Nov 18, 2022 at 13:00
-
@SteveColley Yes,
sp_spaceusedis a handy tool. If it says you're using ~9 GB of data, then you're really using 9 GB of data internally to the database file (theReserved,Data, andIndexsections). You can run it passing in a table name as a parameter to find which tables are using the most space.– J.D.Nov 18, 2022 at 13:06