At the company I work for, we have encountered a problem where it seems that SQL Server 2008 R2 is not correctly calculating the free space in databases. As such databases on that server are growing rapidly in disk space usage when they should not be.
We have two database servers running SQL Server 2008 R2 10.50.1600.1 (RTM) on Windows Server 2008 R2 called Server A and Server Z. To our knowledge both servers are identical including the SQL Server configuration.
The databases held on each server are very similar though no two are identical. All the databases are all set to Simple recovery mode.
Each night on the worst affected databases, a data import is run which deletes around 3 GB of data from the database and inserts around 3 GB of data, maybe a little more. Normally, the size of the database only grew if the inserted data was larger than the deleted data e.g. if 3GB was deleted and 3.1GB was inserted the database grew by 0.1GB.
On Server A, this has recently changed so that in the above example the database is growing by 3.1GB each time the data is imported. The old data is definitely still being deleted. It is the MDB file which is growing, not the transaction logs.
Most of this occurs in one table. When calculating the size of the data in that table it does not add up to the size reported by SQL Server when looking at the table Properties Data space value. The table has a Clustered Index, which we have both Rebuilt and Reorganised to no significant effect.
In the course of investigating this issue we took a backup of the database and restored it under a different name to Server A and Server Z using the same bak file. Server A reported 9% free space, Server Z reported 75% free space and we were able to shrink the databases accordingly.
Based on this it appears that there is an issue specifically with SQL Server on Server A as the same database is reporting a different amount of free space on SQL Server on Server Z. We have now tried this with two of the affected databases with similar results.
We are however unaware of any differences between these servers, and are unaware of any changes made around this time this issue came to our attention.
Is anyone aware of what could be causing this?
The Fill Factor for the Index is 0 for that table on all the databases on both Server A and Server Z. There are no active traces on the databases. DBCC CHECKDB is reporting no errors.
We aim to be upgrading to SQL Server 2016 by the end of the year. The issue we have is that this is causing us to run out of disk space so we cannot wait that long. Cannot understand why this only just started happening, or why only one server is affected. Will ask about patching SQL server.