Solved

Log Shipping size maintenance

Posted on 2011-09-03
5
399 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
  • 3
  • 2
5 Comments
 
LVL 59

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 59

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 59

Accepted Solution

by:
Kevin Cross earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now