• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

SQL full recovery mode logs

I am running an SQL 2008 database with full recovery mode. The transaction logs are backed up every hour by symantec backup. SQL og files are growing very large. The sql end is managed by a third party who advises the full recovery mode is needed to get the most recent files restored in the event of a DB issue. They advised switching to simple recovery mode at night to trim back the logs and then turning it back to full in the morning.

Is there a better way around this or a way of automating the process. The backup software does not have the ability to flush logs after SQL backup I'm told.
0
Sid_F
Asked:
Sid_F
2 Solutions
 
Steve WalesSenior Database AdministratorCommented:
First, have a read of this article:

http://www.experts-exchange.com/Database/MS-SQL-Server/A_11077-How-to-shrink-a-bloated-log-file.html

Unless there has been an extended period of no transaction log backups, you really only  need your transaction log to be as big as the largest transaction volume you expect between transaction log backups.

You shouldn't need to regularly shrink your transaction logs otherwise.

Once the TLogs are backed up the file is marked to be able to be reused.
0
 
Scott PletcherSenior DBACommented:
>> Is there a better way around this[?] <<

Yes.  If the logs get too large with an hour's worth of processing at night, increase the log backup frequency to, say, every 15 minutes (or whatever fraction of an hour keeps the log to the size you want).

You should not switch to simple mode just to "trim" the logs.
0
 
PadawanDBAOperational DBACommented:
Steve's blog post covers the how and the why you shouldn't quite nicely (smack the hand of the person who told you to switch from full recovery to simple recovery to truncate and then back to full - it's a fantastic way to completely circumvent the purpose of full recovery and introduce physical fragmentation of the transaction logs on disk - which are sequentially written to and thus more adversely impacted by fragmentation).  

I would take Scott's suggestion a little further and suggest you give a read to: http://www.brentozar.com/archive/2014/02/back-transaction-logs-every-minute-yes-really/.  As always wonderfully colorful wordsmithing in the post as well!

Another thing: if the backup software can't move the transaction log lsn pointers after it takes a transaction log backup, I would argue it's probably time to either: 1) switch to native SQL Server backups or 2) find new backup software.
0
 
Sid_FAuthor Commented:
Thanks.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now