Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chulheekim
Starting Member
46 Posts |
 Posted - 2014-12-04 : 16:11:50
|
| I recently set up a brand new backup plan. It's been only a couple of days. This is how it looks. I have a daily full backup at 9 PM and Hourly TLog backup 5 AM - 11 PM. The full backup file size is very static and only 600 MB. However, here's my concern. I don't understand why TLog backup file is so big and keeps growing. I thought it will gets smaller every time TLog backup is done. FYI, I save each TLog backup in a different name with INIT and next day restart all over again.Full Backup: 9 PM Nightly (627 MB)TLog Backup: 5 AM (147 MB)TLog Backup: 6 AM (166 MB)TLog Backup: 7 AM (186 MB)TLog Backup: 8 AM (207 MB)TLog Backup: 9 AM (236 MB)TLog Backup: 10 AM (272 MB)TLog Backup: 11 AM (301 MB)TLog Backup: 12 AM (332 MB)Here's the script for the Tlog backup.BACKUP LOG DBName TO DISK = @Path WITH NO_TRUNCATE, INIT |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-12-04 : 16:30:09
|
| Remove NO_TRUNCATE from your BACKUP command.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
chulheekim
Starting Member
46 Posts |
Posted - 2014-12-04 : 16:39:54
|
| No difference. It still keeps growing. :( |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-12-04 : 16:58:56
|
| Do you have open transactions, replication or mirroring? Check sysprocesses for open transactions. Also check sys.databases for why the log can't be cleared (log_reuse_wait_desc column).Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
chulheekim
Starting Member
46 Posts |
Posted - 2014-12-04 : 17:42:16
|
| Never mind. For some reason, the second run after the change made it smaller.TLog Backup: 5 AM (147 MB)TLog Backup: 6 AM (166 MB)TLog Backup: 7 AM (186 MB)TLog Backup: 8 AM (207 MB)TLog Backup: 9 AM (236 MB)TLog Backup: 10 AM (272 MB)TLog Backup: 11 AM (301 MB)TLog Backup: 12 AM (332 MB)TLog Backup: 1 PM (387 MB)TLog backup: 2 PM (12 MB)Now I'm happy with the TLog backup file sizes. However, any reason the TLog file itself still stays the same size 387 MB? The primary db file size is only 600 MB. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-12-04 : 17:46:37
|
| A backup does not shrink the physical file. If you would like to shrink it, you can run DBCC SHRINKFILE. If it were me though, I wouldn't bother since the file is so very small.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
chulheekim
Starting Member
46 Posts |
Posted - 2014-12-04 : 18:38:54
|
| Thank you so much. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
TLog Backup grows big
Tara Kizer