January 13, 2012 at 9:05 am
This is weird. I have a database that is approximately 4 Gb, but it's daily backups are over 30 Gb each. The transaction logs are 39 Gb while it's backups average about 140 Mb. I tried to shrink the log, but it's only got 1 Mb of free space. Is this somehow a problem with the transaction logs not being properly truncated? How can I solve this so I don't have 30+ Gb of .bak files for only a 4 Gb database?
Cheers,
Tom
January 13, 2012 at 9:11 am
Is the database being logshipped or replicated by any chance?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 13, 2012 at 9:23 am
or one of the cause listed here :
Factors That Can Delay Log Truncation
January 13, 2012 at 9:27 am
It is not being log shipped, but I do have mirroring configured, though it has been suspended for some time.
January 13, 2012 at 9:28 am
You found your reason !
log_reuse_wait = 5 ; DATABASE_MIRRORING
Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database (full recovery model only).
For more information, see "Database Mirroring and the Transaction Log," later in this topic.
January 13, 2012 at 9:31 am
Full backups will include some log records and they may have lots of yours because of the suspension. That's definitely the log issue.
Remove mirroring or get it working again.
January 13, 2012 at 9:31 am
Yup. I saw that in the link you posted. That explains why the log is not being truncated for certain. Does that also explain why the full backups are nearly 10x the size of the actually database?
January 13, 2012 at 9:35 am
Thank you for your help. I have removed mirroring. I will watch and see what, if anything, this does for the full backups as well.
Thanks again for your help!
Tom
January 16, 2012 at 1:50 pm
thotvedt (1/13/2012)
Yup. I saw that in the link you posted. That explains why the log is not being truncated for certain. Does that also explain why the full backups are nearly 10x the size of the actually database?
Yes it does because your full backup includes the log
Jared
CE - Microsoft
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy