We have a business application using SQL Server 2005 with a considerably big DB size (almost 80GB) . We have configured a DR setup to copy data from primary database (PR) to the DR server with SQL Server LOG SHIPPING method. The PR data is log shipped on an interval of 15 minutes to the DR Sql Server DB. The problem we are facing is at times the transaction log file of the PR server grows beyond a limit and impacts normal application performance resulting in the application becomes non-responsive. To bring it to normal stage we have to then forcefully run Transaction log backup with truncate only command which reduces the transaction log backup size. But then this impacts our DR setup Log Shipping jobs and it breaks/stops to carry out DR log shipping.
I need help on how to keep the DR setup in sync throughout and keep the transaction log backup at lower space.
Thx for your reply. Is this the only option available? Can this be automated with some script? (I mean, unfortunately we dont have 24/7 DBA to do this manually). Secondly, will the regular transacion log backup reduce the log file size ?
I would not go so far as to say it is the only method available. I am not even sure it will work, as I have not worked with log shipping in a number of years. Is the problem the size of the log file (which will not be automatically reduced), or the number of entries in the log? How big are the data portion and the log portion, now?