2

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.

Improve this question
13
  • To state an obvious point - if you've got a good idea how big the table was when it started, you should be able to guesstimate when the problem started based on how much has been added. You noted no changes from the time when you noticed the problem; does this calculation widen the window to one where a change might have happened? Also, to be on the safe side, I'd double-check @@version on both servers, and the settings for all server-level configurations, and make sure they're the same.
    – RDFozz
    Commented Jun 1, 2017 at 15:40
  • Did you try to rebuild the table on the affected server by restoring the backup? I would not assume that this is anything related to 'calculating free space', it does sound like a fragmentation problem to me.
    – eckes
    Commented Jun 2, 2017 at 7:56
  • If you run select * from sys.configurations on both servers, are there any differences if you compare the values? Did you run a DBCC CHECKDB against the databases?
    – John K. N.
    Commented Jun 2, 2017 at 8:33
  • @RDFozz we have been roughly able to determine when this started as you have described, unfortunately this time frame still does not include any known changes to the servers. We have double checked the @@version and the server configuration and cannot find any differences. Thanks
    – jtrack101
    Commented Jun 2, 2017 at 8:40
  • 1
    @eckes We have rebuilt and reorganised the index on the table, we later took a backup of the database and restored it under a different name on both Server A and Server Z. The relevance of the free space is that exactly the same database shows vastly different amounts of free space on both Servers indicates to me that there is a Server issue and this is not a database problem. It also seems to me that if the database mistakenly thought that space was not free it would grow the database rather than using the existing free space. Thanks
    – jtrack101
    Commented Jun 2, 2017 at 9:05

1 Answer 1

Reset to default
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.