![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(0, 12%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERK%3C/text%3E%3C/svg%3E)
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(28.799999999999997, 2%, 12%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESX%3C/text%3E%3C/svg%3E)
The difference in free space between an on-premises SQL Server database and an Always On configured database in SQL Server 2019 can be attributed to how SQL Server handles free space in different scenarios.
In an on-premises SQL Server database, free space within data files is managed by the SQL Server storage engine. When data is deleted or truncated, the space is marked as free within the data file, but it is not immediately released back to the operating system. Instead, the space is kept within the data file as free space, which can be reused for new data in the future. This is known as "free space reclamation" and is a standard behavior in SQL Server.
On the other hand, in an Always On configured database, which is part of a SQL Server Availability Group (AG), free space is managed differently. In an AG, multiple SQL Server instances work together to provide high availability and fault tolerance. Data changes made on the primary replica are synchronized to one or more secondary replicas. The secondary replicas apply these changes to their own copies of the database. However, the secondary replicas do not perform free space reclamation. Instead, the free space is preserved in the data files to ensure consistency with the primary replica.
As a result, when you compare the free space in data files between an on-premises SQL Server database and an Always On configured database, you may see that the on-premises database has more free space, while the Always On configured database has less free space. This is because the free space in the Always On configured database is being preserved to maintain consistency among the replicas in the Availability Group.
It's important to note that this behavior can be different when you restore an Always On configured database to a standalone SQL Server instance, as the free space is reclaimed by the storage engine in the standalone instance, leading to potentially more free space in the data files. However, in the context of an Always On Availability Group, the behavior is as described above to ensure data consistency among the replicas.
Best regards,
Seeya
If the answer is the right solution, please click "Accept Answer" and kindly upvote it.