Solved

Log Shipping size maintenance

Posted on 2011-09-03
5
398 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

12 Experts available now in Live!

Get 1:1 Help Now