Log file doesn't clear after backup

Question

Log file doesn't clear after backup

Samwell

Mr or Mrs. 500

Points: 579
More actions
September 17, 2018 at 11:32 am

#408460

I have two databases on a sql server 2008 r2 instance that are mirrored to a second server. I do backups on the principal server daily as well as an incremental log file backup. I expect the log file to be relatively small because they aren't large databases (less than 1GB) but it never changes size or percentage used. I'm using dbcc sqlperf(logspace); to find the size and percentage used.
Database Name Log Size (MB) Log Space Used (%) Status
db1 9549.742 94.92307 0
db2 25495.99 93.0863 0
I did some manual backups today just to try to get the log file to give up some space so that I could shrink it and no luck. Any ideas?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Samwell - Monday, September 17, 2018 11:32 AM
I have two databases on a sql server 2008 r2 instance that are mirrored to a second server. I do backups on the principal server daily as well as an incremental log file backup. I expect the log file to be relatively small because they aren't large databases (less than 1GB) but it never changes size or percentage used. I'm using dbcc sqlperf(logspace); to find the size and percentage used.
Database Name Log Size (MB) Log Space Used (%) Status
db1 9549.742 94.92307 0
db2 25495.99 93.0863 0
I did some manual backups today just to try to get the log file to give up some space so that I could shrink it and no luck. Any ideas?

Depends on transaction safety settings for the mirroring.
If you use synchronous mirroring then the primary log records will be active until the records are hardened on the mirror and the acknowledgement is sent back to the primary. If you want to see how much of the log is active, execute dbcc loginfo() on the database which will show you the VLFs and the status (2 is active).

Sue

Samwell Mr or Mrs. 500 Points: 579 More actions · Answer 2

I'm now seeing that mirroring is suspended on these databases and that may be the root of my problem. I am checking with the software vendor to determine next steps. Thanks for the quick response and the explanation regarding hardening of transactions.

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 3

Select name, log_reuse_wait_desc from sys.databases will give you the reason the transaction log is not being truncated. Also full backups do not truncate the transaction log, just in case those were the manual backups you were doing (it's not clear).