Database too big than .bak

Question

Database too big than .bak

tmmutsetse

Hall of Fame

Points: 3669
More actions
April 3, 2018 at 6:36 am

#403224

Good day,
I have a DB which is 850 GB in size and when i take a full backup the backup size is 160 GB.On the DB properties its showing 9GB space available.
Any idea why this big difference in size.
Thanks
T

Viewing 13 posts - 1 through 12 (of 12 total)

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

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 1

tmmutsetse - Tuesday, April 3, 2018 6:36 AM
Good day,
I have a DB which is 850 GB in size and when i take a full backup the backup size is 160 GB.On the DB properties its showing 9GB space available.
Any idea why this big difference in size.
Thanks
T

Thats the free space available in your database which will be used for later growth. The backup size seems fine.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Is the backup compressed?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tmmutsetse Hall of Fame Points: 3669 More actions · Answer 3

tmmutsetse

Hall of Fame

Points: 3669

April 3, 2018 at 9:08 am

#1985309

@Gila The backup is on the default server setting

tmmutsetse Hall of Fame Points: 3669 More actions · Answer 4

@ VastSQL - You mean its normal 850 GB mdf to 160 GB. bak difference?

Ant-Green SSC Guru Points: 113507 More actions · Answer 5

Whats the output of this code snippet?

select value_in_use from sys.configurations where name = 'backup compression default'

If it's 1 the default setting is to compress and that would correlate with the backup size your seeing.

You could also query msdb.dbo.backupset and check the backup_size vs the compressed_backup_size

jasona.work SSC Guru Points: 50118 More actions · Answer 6

tmmutsetse - Tuesday, April 3, 2018 9:08 AM
@Gila The backup is on the default server setting

If you run the following query:

exec sp_configure 'backup compression default';

What is the "config_value"?
If it's set to 1, then the server by default will try to compress your backups.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

tmmutsetse - Tuesday, April 3, 2018 9:08 AM
@Gila The backup is on the default server setting

And is the server default to compress backups?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tmmutsetse Hall of Fame Points: 3669 More actions · Answer 8

@Gila the server is ticked to compress backup on server properties.

@antony this code (select value_in_use from sys.configurations where name = 'backup compression default') does not return anything.

Thank you for your willing to help.

VastSQL SSC-Dedicated Points: 36121 More actions · Answer 9

tmmutsetse - Tuesday, April 3, 2018 9:13 AM
@ VastSQL - You mean its normal 850 GB mdf to 160 GB. bak difference?

What kind of data it holds ? Is the data getting loaded by bulk operation?

I have a db with 48GB , 5GB backup with 380 MB freespace

tmmutsetse Hall of Fame Points: 3669 More actions · Answer 10

tmmutsetse

Hall of Fame

Points: 3669

April 4, 2018 at 5:47 am

#1985430

Yes the data is loaded in bulk through RabbitMQ

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

tmmutsetse - Wednesday, April 4, 2018 3:17 AM
@Gila the server is ticked to compress backup on server properties.

There you go then. The backup is compressed, and hence is a lot smaller than the data size of the database.
It's not related to how you load the data, it's simply that the backup is being compressed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

andreas.kreuzberg SSCertifiable Points: 6191 More actions · Answer 12

Hi,
try this query to check your backup-compression-ratio:

SELECT [database_name], [backup_size], [compressed_backup_size], [backup_finish_date], CAST(backup_size/compressed_backup_size AS DECIMAL(16,2)) AS backup_compression_ratio FROM msdb..backupset;

It depends on the data, but the compression ratio could be big.
Kind regards,
Andreas