Sql-server – Will DIFF backup size be reduced if we shrink the data files

sql server

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?

Best Answer

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).

Related Question