November 14, 2019 at 7:25 pm
Here's a head scratcher - I've not seen anything like this in 20+ years of DBA life!
I received a SQL Server 2008 R2 backup file to restore. There wasn't enough disk space on the server so I moved the file to my workstation to look at it. After restoring the database, the transaction log file was 190 GB in size. The database was in FULL recovery mode, and my guess is that it had never had a transaction log backup performed. Yeah, I know... that's another topic for another day.
So, I used dbcc shrinkfile (2,1) to shrink the log file, with log backups to 'nul' to clean out everything from the log file. I did perform one database backup to 'nul' just to get the backup file chain going, since this is all running on a different computer.
Now, here's the strange part. The original backup file is 197MB in size... and the 'after' backup file is 599MB in size! How does a backup file triple in size, with no transactions taking place, and shrinking the log file to 1MB??
November 14, 2019 at 7:34 pm
Oh, I forgot - I set the database to SIMPLE recovery mode after shrinking the log file and before creating the final backup file. I don't think this would make a difference, but who knows?
November 14, 2019 at 8:12 pm
original backup was created with compression - the one you did on your workstation didn't use the compress option
November 14, 2019 at 8:49 pm
@frederico_fonseca - That's a good point. I don't know if the original backup was created with compression or not. I can't test it out on my instance b/c I'm running Express edition, I'll have to try that out later on a different edition.
3X the size difference? That's amazing. Thanks for the answer. Cheers!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply