Solved

SQL full recovery mode logs

Posted on 2014-03-19
4
380 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

752 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