?
Solved

Log Shipping size maintenance

Posted on 2011-09-03
5
Medium Priority
?
406 Views
Last Modified: 2012-05-12
I'm running SQL Server 2005 and have log shipping configured for our Production database. Both the Primary and Secondary servers are running Full Recovery models. The Secondary server is configured Standby/Read-only. I also have a full nightly backup of our Production database. After implementing Log Shipping I'm trying to find a solution to maintain the Production's log .ldf file to a minimum because this file gets over 100GB every week. I found truncate log breaks the sequence of Log Shipping and read that I should run the shrink file command manually. I'm not clear on this recommendation but can anyone recommend an automated solution to keep the log file from growing this large while Log Shipping is configured?
0
Comment
Question by:apimentel26
[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
  • 3
  • 2
5 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36479590
Do you have periodic transaction log backups already configured? If not, you may want to schedule those. I have mine starting around 8 a.m. and going every hour until about 8 p.m. then I have a database backup and start again the next day. Each situation is different, though, and so find the schedule that fits your needs best.
0
 

Author Comment

by:apimentel26
ID: 36479596
The periodic transaction log are coming from Log Shipping. I have full nightly SQL backup that runs and I also have Backup Exec job as well. I'm trying to find a better solution for this issue log file increase. What should I do?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36479628
*smile* As soon as I posted I thought to myself...it has been awhile since I did log shipping. You are right. The combination is the problem. Log Backups truncates the log which is what you want, but would likely not be good in conjunction with log shipping. One suggestion would be to use just normal backups and then roll your own log shipping from the disk-based transaction log files. There is some instructions on this recommendation -- I will find the link.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36479642
Here are some nice references:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_657-Managing-the-Transaction-Log-for-the-Accidental-DBA.html

http://www.sqlservernation.com/home/why-is-my-log-file-so-big.html
http://www.techrepublic.com/blog/datacenter/help-my-sql-server-log-file-is-too-big/448

In the last article, Tim talks about Database Mirroring which is another option. Hoping between the three you get some good information on transaction log management and can see what will be the best setup for you for blend of fault tolerance and disk space (log size) management.
0
 

Author Comment

by:apimentel26
ID: 36490971
Ah Thank You! Now I have a better understanding.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

718 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