February 17, 2012 at 4:31 pm
When I shrink a log file (by 250 gb) using
DBCC SHRINKFILE (myFile_Log, 1);
and then create a new compressed backup, why does my .bak file end up bigger than it was to begin with?
February 17, 2012 at 5:39 pm
Slight correction- actually the new .bak is 500 mb smaller than the original. But even compressed shouldn't a 250 gb reduction in log file size result in more than 500 mb reduction in backup file?
I started by restoring a myFile.bak (181 gb)-
Truncated 250 gb from the log file-
Created a new compressed backup-
Resulting in a myFile.bak (180.5 gb)
Doesn't seem right....
February 17, 2012 at 11:57 pm
The inactive portion of the log is freed by log truncation. Your log is 250GB but you are no longer backing up anything in it since you already did a full db backup. Shrinking it will not help your db size.
http://technet.microsoft.com/en-us/library/ms189085.aspx
However, the first thing I noticed about your post is that your DB backup is 180Gb and your log is 250GB. Even with compression, that is one large log file compared to the overall db size. Are you backing up your log file regularly? Like every 30 minutes?
February 18, 2012 at 2:34 am
The entire log is not included in a backup, the only portion of the log that is is (more or less, roughly) the portion written over the period of the backup and a bit from before the backup started, so removing empty space from a file wouldn't change that and hence wouldn't change the backup size.
The size of a backup is roughly the size of the data in the database.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply