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.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 TLog Backup grows big

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

chulheekim
Starting Member

46 Posts

Posted - 2014-12-04 : 16:39:54
No difference. It still keeps growing. :(
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

chulheekim
Starting Member

46 Posts

Posted - 2014-12-04 : 18:38:54
Thank you so much.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-04 : 18:51:43


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -