Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

Log Shipping size maintenance

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
apimentel26
Asked:
apimentel26
  • 3
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
 
apimentel26Author Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
*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
 
Kevin CrossChief Technology OfficerCommented:
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
 
apimentel26Author Commented:
Ah Thank You! Now I have a better understanding.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now