Solved

SQL full recovery mode logs

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Upgrading my SSIS package in VS 2012 6 62
SQL Log size 3 20
SQL Group By Question 4 20
Use column to search string column 2 11
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

828 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