Solved

SQL full recovery mode logs

Posted on 2014-03-19
4
387 Views
Last Modified: 2014-03-20
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
Comment
Question by:Sid_F
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 250 total points
ID: 39939984
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39940540
>> 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
 
LVL 10

Accepted Solution

by:
PadawanDBA earned 250 total points
ID: 39940801
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
 
LVL 6

Author Closing Comment

by:Sid_F
ID: 39941732
Thanks.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question