Menu
I have a SQL Server DB that is about 13Gb with a 1.6Gb log file. When I back it up, however, it creates a 50Gb .bak file! Why is that?
I have read some commentary here about transaction logs, but my DB runs on my laptop and there is no DB activity during the time of the backup. (It only takes 5 minutes).
My backup type is Full, my recovery type is Full, and I am doing Copy-Only backups. Any ideas? Much appreciated!
I am using SQL 2008 R2, Developer Edition.
923
If it is the backup that keeps growing and growing, I had the same problem. It is not a 'problem' of course, this is happening by design - you are just making a backup 'set' that will simply expand until all available space is taken. To avoid this, you've got to change the overwrite options.
To shrink a data or log file. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance. Expand Databases and then right-click the database that you want to shrink. Point to Tasks, point to Shrink, and then select Files.
You shouldn't be shrinking your database unless you had an unusually large transaction go through. If you shrink the logs they will grow right back to the same size.
It might be because the backup routine is appending the new backup to the same file?
There is an option in the Backup dialog in Sql Management Studio to Append or Overwrite all existing backup sets. If you have Append if will add the backup to the same .bak file and the .bak file will be larger for every backup.
In Transact Sql it is the INIT and NOINIT option which determine if the bak file is overwritten or appended.
153
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
