I have a server with multiple databases which have mirroring configured as well as logshipping configured to another location. The primary database is in FULlL recovery, my concern is the database log file, I have read that you need to do regular log back ups to ensure the database log file does not grow (I assume that logs shipping will take care of this?) , however the log file seems to be growing daily, do I need to shrink the log file periodically on the primary? Can you shrink the log file on a database which has mirroring configured?
Any help would be great as all of my research seems to be contradicting itself!
Yes log shipping should be taking care of this, but you need to monitor the jobs to make sure it still happens.
Do NOT shrink the log file periodically. Shrinking the log should be done manually and only in rare cases, such as a large purge and you know you won't need that space for months to come.
How often is log shipping backing up the transaction log and are those jobs successful? How big are the data files and how big are the ldf files?
The log growth is likely due to index rebuilds or large transactions from the application. You just need to backup the tlog often enough to account for it. How often is dependent upon your system. We backup our tlogs every 15 minutes. We increase that to every 5 minutes if we have an upgrade happening where we'll be doing a lot of changes.
Thanks Tara, the logshipping is configured to backup every 15 minutes , our largest DB file is 52105 MB (PRIMARY Filegroup) and the log lie is 20126 MB, should we always see an increase in the ldf file?
That log file size sounds fine for that data file size.
The ldf file should not necessarily grow, but it depends on your system. It depends on the largest transaction, which is generally rebuilding indexes. Once you've hit the largest transaction (we can't answer that for you), you will likely not see more growth or very little.