Backup size is bigger than DB size

Question

Backup size is bigger than DB size

THolley

SSCommitted

Points: 1506
More actions
March 13, 2008 at 6:49 am

#184952

My *.bak file is bigger than than my database files.
Backup size last night was 431GB. The database files are 371GB and
My log file is 178GB.
Does the backup file backup the log size in the file as well?
Yes, the Trans logs are backed up every 15min.
Help.

Viewing 15 posts - 1 through 15 (of 19 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 1

Are you using INIT and creating a new file every day? A BAK file can be set to store multiple backups, which is a bad idea. Go to the restore dialog, look at the contents of the file.

THolley SSCommitted Points: 1506 More actions · Answer 2

Steve Jones - Editor (3/13/2008)
Are you using INIT and creating a new file every day? A BAK file can be set to store multiple backups, which is a bad idea. Go to the restore dialog, look at the contents of the file.

I am creating a new file each day. Once the new file is created the old file gets deleted.

THolley SSCommitted Points: 1506 More actions · Answer 3

I starting to think that my log file growth is becuase of stopping the replication log Reader. It was timing out. So I need to redo the snapshot.

My question is

If you have Replication setup does it keep these queries in the trans log file until they are read by the Repliation log ready and transfered to the replicated DB?

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 4

The size of your backup file is smaller than the sum of your data and log files. It is reasonable. From your data, log file takes about 60 GB in your backup. Your server must be used very heavily.

You can use DBCC SHRINKFILE command to shrink your log file.

Do you have any replication or log shipping running on this server? If so, the inactive transaction logs may not be released when replication or log shipping are in progress even you have transaction backups. DBCC SHRINKFILE may not work either.

THolley SSCommitted Points: 1506 More actions · Answer 5

....
Do you have any replication or log shipping running on this server? If so, the inactive transaction logs may not be released when replication or log shipping are in progress even you have transaction backups. DBCC SHRINKFILE may not work either.

First, thanks for getting back with me.

Yes, lots of activity.

Yes/no, Replication was running until last sunday night and it timeout out. I was not able to get the logreader to run, so I just stopped it.

So If I delete the Replacation process on the server, will this release the inactive transactions for the log?

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 6

You bet.

Although you have regular transactional backup, your transactions are kept for your replication. Therefore, we cannot say the transactions are really inactive until you drop the replication, or your replication is committed in transactional replication.

THolley SSCommitted Points: 1506 More actions · Answer 7

Follow-up.

By deleting the Replication process, I was able to shrink the log file down to size.

That was indeed the problem.

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 8

Thanks for the update and be careful of pausing replication for extended periods of time.

sittingbourne_rock SSC Veteran Points: 267 More actions · Answer 9

I have a similar problem: .mdf file is 2.3GB and the .bak file is 3.0GB. The log file was truncated before the backup was created and I don't use replication.

I'm using SQL Server 2000

Any thoughts ?

RichRock

george sibbald SSC Guru Points: 104210 More actions · Answer 10

richrock (7/21/2008)
I have a similar problem: .mdf file is 2.3GB and the .bak file is 3.0GB. The log file was truncated before the backup was created and I don't use replication.
I'm using SQL Server 2000
Any thoughts ?
RichRock

truncating the log won't clear out active transaction so I presume you had a large open transaction at the time of the backup.

---------------------------------------------------------------------

sittingbourne_rock SSC Veteran Points: 267 More actions · Answer 11

Thanks for the fast response. The database isn't subject to large transactions. And the .ldf was 1MB when the backup was created. And - apologies for putting this in "SQL Server 2005 > Backups"!!!!

RichRock

george sibbald SSC Guru Points: 104210 More actions · Answer 12

then we are back to the old appending backups rather than overwriting?

---------------------------------------------------------------------

sittingbourne_rock SSC Veteran Points: 267 More actions · Answer 13

No, that's not it. I created a new backup device and selected "Overwrite existing media" - just in case.

RichRock

george sibbald SSC Guru Points: 104210 More actions · Answer 14

just make sure you are able to restore from this .bak file. will be interesting to see how long the recovery stage takes,.

---------------------------------------------------------------------