Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jstirado
Starting Member
6 Posts |
 Posted - 2009-03-10 : 14:08:48
|
| Few weeks ago I had set up Log shipping to a secondary read only server for our sister company to query our production database and incase we needed an extra disaster recovery option. It works beautifully but I just notice the ldf. Log file on the secondary server has gotten huge after restoring so many T-log backup through out the days. I’ve tried shrinking it but that’s not an option on a read-only database. So what are my options on automating a process that will keep the log file on the secondary server at a minimum? I don’t have much space to play with on the secondary server box. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-03-10 : 14:22:35
|
| If you look, I think you will find that the log files are the same size on both servers.The only way to control the size of the LDF on the secondary server is to reduce the size on the primary server.CODO ERGO SUM |
 |
|
|
jstirado
Starting Member
6 Posts |
Posted - 2009-03-10 : 14:46:01
|
| They are 2 different sizes, trust me. The secondary log file is 19 GB and the primary is 48 MB. I did notice that the Standby file ROLLBACK_UNDO.BAK that I created when originally creating this standby server no longer exists. If that's missing would that cause the transaction log file to grow like that? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-03-10 : 15:03:32
|
| If you have transaction log shipping correctly configured to restore into standby mode on the secondary server, the log file sizes will be the same on both servers.Since you have a missing undo log, it's obvious that transaction log shipping is not working correctly. You need to look into what is going wrong.CODO ERGO SUM |
|
|
|
jstirado
Starting Member
6 Posts |
Posted - 2009-03-10 : 15:37:06
|
| Should it matter that I have backups happening every 15min but restores on the secondary server happening only 3 times a day? Will that cause a difference in log file size between both server? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-03-10 : 23:07:22
|
quote:
Originally posted by jstirado
Should it matter that I have backups happening every 15min but restores on the secondary server happening only 3 times a day? Will that cause a difference in log file size between both server?
Yes this is the cause.This is disadvantage I see in Log shipping.You must be using it for Reporting Needs. Instead use transactional replication. |
|
|
|
jstirado
Starting Member
6 Posts |
Posted - 2009-03-11 : 13:24:56
|
| Replication isn't an option because the ERP DB's configurations of tables does not support replication. Still even if you are doing a restore to the secondary DB 3 x times a day there should not be a difference in log size by that much. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-03-11 : 22:36:18
|
quote:
Originally posted by jstirado
Replication isn't an option because the ERP DB's configurations of tables does not support replication. Still even if you are doing a restore to the secondary DB 3 x times a day there should not be a difference in log size by that much.
why it doesn't support Replication? any specific reasons? |
|
|
|
|
|
|
Log file on the secondary read-only DB is huge