Very High Full Backup size

Question

Very High Full Backup size

rohan1424

SSChasing Mays

Points: 611
More actions
April 14, 2014 at 3:15 am

#287673

On my production sql server , teh mdf size is 400 MB , ldf is about 1.2 GB . If i take full backup , the backup size becomes 32 GB. I have truncated log file by changing the recovery model to simple. Taken trabsaction log backup also . Checked the row counts of all tables also, and its under control.
Any thoughts on the abnormal backup size ?

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

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

SQLCJ SSCommitted Points: 1781 More actions · Answer 1

Are you sure that you are taking the backup of the right database and not appending it to the existing backup file? or at least referring to the right backup file ? never saw such a huge difference .. in fact never saw Full backup (allocated pages + essential portion of a log ) size more than the database size

rohan1424 SSChasing Mays Points: 611 More actions · Answer 2

Thanks for your reply . I m taking backup of the right db. I have even taken a independent backup via command line. But the problem persists. Will appreciate a solution from the experts in this forum.

Yogeshwar Phull Default port Points: 1434 More actions · Answer 3

Can you run below command and see how many files are there in the backupset?

restore headeronly from disk='backup file path'

It might be the case that backups are getting appended to one file and hence showing huge size.

free_mascot One Orange Chip Points: 27169 More actions · Answer 4

can you post result of following command? Where dbname is the database name on which you are facing issue.

sp_helpdb <dbname>

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

HowardW One Orange Chip Points: 29109 More actions · Answer 5

HowardW

One Orange Chip

Points: 29109

April 14, 2014 at 4:43 am

#1705715

Do you use Filestream or Full Text Indexes?

rohan1424 SSChasing Mays Points: 611 More actions · Answer 6

ownerdbidcreated status compatibility_level

sa 5Jan 31 2014Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=706, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled110

================================================================

filename filegroup size maxsize growth usage

D:\MainDB\demo.mdf PRIMARY 405504 KBUnlimited 1024 KB data only

D:\MainDB\demo_log.ldf NULL 1341696 KB 2147483648 KB10% log only

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 7

Does your backup use WITH INIT? It really sounds like the backup is stacking up. What is the precise command you're using to do the backup. If you're using the GUI, script the command out using the button on the top of the window.

Also, is your database in FULL recovery? If so, are you running log backups? You need to if the database is in FULL recovery.

I've never seen a full backup multiply the size of the database. It's only going to backup the pages of the data files themselves (usually smaller than the database) and the pages of the transaction log. For it to suddenly take what should only be close to 2gb and turn it into 32gb, something else that you're not telling us is occurring.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

rohan1424 SSChasing Mays Points: 611 More actions · Answer 8

Thanks Grant for for advice . The issue has been identified . withnoinit command was part of the backup job which was causing to append the backup file to the backupset , hence the 32 GB backup.My recobery model is full. I have regular log backups and even truncate logs after changing the recovery model.

Once again thank you all for your time !