Backup size is doubled now. Want to know the reason why ??

Question

Post reply

Backup size is doubled now. Want to know the reason why ??

vsamantha35

SSChampion

Points: 11551
More actions
August 11, 2019 at 11:39 am

#3670528

Hi All,
Yesterday, we had a big Prod release.
Pre- release we have taken backup. The backups size was around 456.6 GB.
Post- release we have taken backup. Backup size is almost doubled 822 GB.
I have collected the sp_helpfile output before and after. I dont see any big change. But why the backup is so big? it is a compressed backup. May I know the reason why the backup is increased because I am seeing the sp_helpfile output before and after almost same??
Before release
==================
name fileid filename filegroup size maxsize growth usage
missouri_ors_prm 1 E:\DATA\missouri_ors_prm.mdf PRIMARY 5509888 KB Unlimited 102400 KB data only
missouri_ors_log 2 V:\MSSQL\Log\missouri_ors\missouri_ors_log.ldf NULL 65150976 KB Unlimited 1048576 KB log only
missouri_ors_dat 3 K:\MSSQL\Data\missouri_ors\missouri_ors_dat.mdf missouri_DATA 2593993856 KB Unlimited 102400 KB data only
missouri_ors_ind 4 E:\DATA\missouri_ors_ind.mdf missouri_INDX 366326528 KB Unlimited 102400 KB data only
After release
==================
name fileid filename filegroup size maxsize growth usage
missouri_ors_prm 1 E:\DATA\missouri_ors_prm.mdf PRIMARY 5509888 KB Unlimited 102400 KB data only
missouri_ors_log 2 V:\MSSQL\Log\missouri_ors\missouri_ors_log.ldf NULL 26484736 KB 2147483648 KB 1048576 KB log only
missouri_ors_dat 3 K:\MSSQL\Data\missouri_ors\missouri_ors_dat.mdf missouri_DATA 2593993856 KB Unlimited 102400 KB data only
missouri_ors_ind 4 E:\DATA\missouri_ors_ind.mdf missouri_INDX 366326528 KB Unlimited 102400 KB data only
Thanks,
Sam

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Alejandro Santana SSCommitted Points: 1838 More actions · Answer 1

Is it a differential or a full backup?

The time it took was the doubled or more?

Everytime you make a backup SQL server writes in the log the amount of pages it processed when the backup is finished, can you get that info?

vsamantha35 SSChampion Points: 11551 More actions · Answer 2

Hi Alex,

Its is a full backup. Don't know the reason why , when backup is in progress , it was showing the backup file size as 822 GB but after backup completion it is 473 GB which is expected. Don't know why. now everything looks fine.

Thanks for the quick response.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

vsamantha35 wrote:

Hi Alex,
Its is a full backup. Don't know the reason why , when backup is in progress , it was showing the backup file size as 822 GB but after backup completion it is 473 GB which is expected. Don't know why. now everything looks fine.
Thanks for the quick response.

SQL Server utilizes a pre-allocation size for the backup because it cannot know what size the file will be after it has been compressed. See this document: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-compression-sql-server?view=sql-server-2017

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

You say it was a BIG prod release... how much of it involved expansion of data and the addition of a lot of indexes, especially clustered indexes and large covering indexes? Also, if it involved changes to data or the addition of columns, there could have been massive page splits involved where many of your Clustered Indexes are now only 50% full, or less.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vsamantha35 SSChampion Points: 11551 More actions · Answer 5

Thanks Jeffrey Williams. Thanks for the link as well.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Jeffrey Williams wrote:

vsamantha35 wrote:
Hi Alex,
Its is a full backup. Don't know the reason why , when backup is in progress , it was showing the backup file size as 822 GB but after backup completion it is 473 GB which is expected. Don't know why. now everything looks fine.
Thanks for the quick response.
SQL Server utilizes a pre-allocation size for the backup because it cannot know what size the file will be after it has been compressed. See this document: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-compression-sql-server?view=sql-server-2017

But is does go down after the backup is complete. It may or may not be automatically updated in your view of Windows Explorer, so don't forget to hit the "Refresh" button. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 7

vsamantha35 wrote:

it was showing the backup file size as 822 GB but after backup completion it is 473 GB which is expected

Are you using compression?

prettsons SSCertifiable Points: 7453 More actions · Answer 8

When you take a backup, you have the following options:

Media Options:

Option to overwrite media

Append the existing backup set
Overwrite all existing backup set

backup of sql

In my case, I used the overwrite option when doesn't require old backup.

You can also try an appropriate option to control database size.

This is my suggestion for you.

SQL Database Recovery Expert 🙂

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

prettsons wrote:

You can also try an appropriate option to control database size.

So long as it's not "AutoShrink", DBCC SHRINKDB, or DBCC SHRINKFILE (especially since they don't affect backup size).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)