SQL Server Backup Huge

Question

Post reply

SQL Server Backup Huge

Welsh Corgi

SSC Guru

Points: 116520
More actions
January 21, 2014 at 2:44 am

#281438

I'm running a SQL Server Backup on a 350 + GB Database.
The normal size if about 49 GB for a compressed backup but the backup starts with over 100 GB in size.
The transaction log had grown to over 100GB but I shrank it. Someone deleted the Backup jobs which caused the log file to grow.:w00t:
Any ideas about how this could be happening?
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 5 posts - 1 through 5 (of 5 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

It is ok. The size of the file went from over 10 Gb to 49 GB.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 2

A full backup includes the part of the transaction log that hasn't been marked as getting removed, so if you had a huge jump in the size of the log due to the backups stopping, that would be reflected in the size of the full backup.

"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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

When running compressed backups, SQL initially allocates a file that may well be larger than the final backup will be. This is so that it doesn't have to grow the backup file during the backup.

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Thanks. Your responses are very much appreciated. I'm amazed at you knowledge and expertise.

I have another weird one related to the transaction log.:w00t:

http://www.sqlservercentral.com/Forums/Topic1533161-1550-1.aspx

Thanks!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/