Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL full recovery mode logs

Posted on 2014-03-19
4
Medium Priority
?
410 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
4 Comments
 
LVL 23

Assisted Solution

by:Steve Wales
Steve Wales earned 1000 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 70

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 1000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

885 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