Manage Log file growth

Question

Manage Log file growth

DKlein

SSCrazy

Points: 2351
More actions
August 5, 2013 at 11:59 am

#280871

All,
We made the switch to Sql Server '12 over the weekend and our main app database log file is slowly growing, past the point where I feel comfortable. We have run a full backup, a differential last night and transaction logs every 15 minutes. But the log file keeps growing and is not shrinking. We need to have the database in full recovery as it is the primary database for a transactional replication publication and the primary database in a availability group. Replication is only 6 second delay and the availability group is up to the second. Any suggestions?
Thanks in advance,
DK

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

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

MarlonRibunal Ten Centuries Points: 1332 More actions · Answer 1

sorry, this is not a direct response to your question, but there's a good section about your question in the Red Gate book "SQL Server Transaction Log Management" http://www.red-gate.com/community/books/sql-server-transaction-log-management (pdf download is free)

SQL Server Database Administrator

jasona.work SSC Guru Points: 50121 More actions · Answer 2

I'd say the first thing to do, is see what SQL says is the reason for not re-using the log space:

use [master];

select name, log_reuse_wait_desc from sys.databases;

Could be a long-running transaction, could be a problem with your log-shipping (which based on what you mentioned, it sounds like you're doing.)

Jason

DKlein SSCrazy Points: 2351 More actions · Answer 3

Thanks for the responses.

jasona.work, the reason was 'Log_Backup', so I increased the frequency of log backups. Will repost results.

Thanks again,

DK

DKlein SSCrazy Points: 2351 More actions · Answer 4

I think I found it, we used maintenance plans with ssis packages. The package was doing a check to see if the database on this server had the advantage in weight, it did not:

DECLARE @preferredReplica int

SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica('DatabaseName'))

select @preferredReplica

IF (@preferredReplica = 1)

BEGIN

BACKUP LOG [DatabaseName] TO DISK = N'C:\Backup\DatabaseName\Log\DatabaseName_backup_2013_08_05_160551_3886538.trn' WITH NOFORMAT, NOINIT, NAME = N'DatabaseName_backup_2013_08_05_160551_3886538', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10

END