2

In our Prod env, DIFF backup job was failing due to insufficient space in the drive ( Total free space 36 GB ). That means DIFF backup file was generating more than 36 GB. I investigated and found that the DB size 80GB has 70GB of free space. I immediately cleared 60GB space from data files and then I was able to take DIFF backup successfully( DIFF Backup size generated 10 GB).

Given that backups only take backup of data inside the DB not free space, why did the above scenario happened?

  • 1
    just a though well if the excluding the free space full backup will be 10 gb but how come a diff will 10 gb ? so the full backup was taken at around 1gb? – Biju jose Jul 4 '18 at 13:49
  • 1
    Are you aware about backup compression it is there is standard and enterprise edition of SQL Server 2008 R2 onward. – Shanky Jul 4 '18 at 16:41
  • 1
    What did you do exactly to "immediately cleared 60GB space from data files"? How did you measure "DB size 80GB" and "70GB of free space"? – mustaccio Jul 4 '18 at 17:48
1

A differential backup actually only reflects the changes that were made on your database since the last full backup; more specifically: the extents that were changed since your last full backup.

A reason for the differential backup still backing up the no longer used extents may be that you have deleted a bunch of data using TSQL DELETE and not dropped the tables involved. From https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql?view=sql-server-2017:

When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database.

Since the pages remain allocated, they will also be included in the backup.

My guess is that a DBCC SHRINKFILE will deallocate the empty pages, thus decreasing the size of DIFFERENTIAL backups, however I was not able to confirm this.

Shanky's comment of course also is a valid question as backup compression surely will not store empty pages themselves but only a reference to them and therefore vastly make a difference in size for you (in case you have Enterprise Edition, though).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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