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.
Best Answer
Rebooting the server has fixed the issue
After rebooting Server A the correct amount of free space was showing on the affected databases on Server A.
e.g. for the example in the question the affected database that was showing 9% free on Server A and 75% free on Server Z is now also showing 75% free on Server A after rebooting Server A.
We have now shrunk the affected databases on Server A back to having a normal amount of free space and run the imports. The databases have only grown by the amount of new data that has been added. e.g. if 3GB deleted and 3.1GB inserted the database has grown by 0.1GB instead of 3.1GB.
For reference Server A had been rebooted 3 weeks before and we are now planning to upgrade to SQL Server 2016 in the next couple of months.