Solved

Log Shipping size maintenance

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

Title # Comments Views Activity
Record open by another user 6 58
Caste datetime 2 57
Restrict result set 1 38
My Query is not giving correct result. Please help 5 40
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

770 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