asked
Database Mirroring
Recently ,I have configured Database mirroring on SQL Server 2008R2 Standard Edition. It has been configured successfully but LDF is getting increased automatically very fast day by day. Database size is 4 GB and ldf size became 40 GB within one week. How can resolve this problem. Kindly help me.
This web site runs off of votes. Can you please indicate all helpful answers below by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that one answer.
I have implemented the log shipping three month ago . Whenever log increase automatically . I ran this script --SELECT * FROM sys.database_files Use DatabaseName GO Alter Database DatabaseName Set Recovery Simple GO Alter Database DatabaseName Set Recovery Full GO DBCC SHRINKFILE ('LogFileName', 1) GO DBCC SHRINKFILE ('LogFileName', 1) GO DBCC SHRINKFILE ('LogFileName', 1) GO DBCC SHRINKFILE ('LogFileName', 1) GO DBCC SHRINKFILE ('LogFileName', 1) GO DBCC SHRINKFILE ('LogFileName', 1) GO after that log gets shrunk ,Then I restore diff over it because that script breaks the Log sequence Number (LSN) and then L.S Start again and work fine. I tried to restore diff in database mirroring but It didn't restore. Can I restore Diff in database mirroring. If yes then how can.
I read above link .According to them.I have configured properly database mirroring.First I restored the last full backup then last differential backup then Transaction backup but I want to know that During making the maintenance plan for Trans log backup , I have to choose overwrite or append option which one is correct.I chose overwrite . I think this is the reason due to which I am not able to restore either diff or trans backup in data mirroring.I am asking because I never made trans backup till log shipping .
Questions: 1. Are you doing log backups on the source database? 2. Is there a long-running transaction preventing recycling of log (check `DBCC OPENTRAN`)
http://msdn.microsoft.com/en-us/library/ms182792.aspx
Thomas nailed both of the causes I would have suspected. In my experience, it's typically the first cause. This definitely sounds like a case of a database in full recovery mode (which is, of course, required for mirroring) without any log backups being taken. If that is the case, scheduling regular log backups will resolve it. Just make sure you also have a maintenance plan for your backup files or else you'll just shift the storage problem from a database to a folder.
I'd suggest that you take a look at this link on the recommended way to configure mirroring and make changes to your configuration to match this. Database Mirroring (SQL Server)
http://msdn.microsoft.com/en-gb/library/ms189852.aspx Setting Up Database Mirroring
http://msdn.microsoft.com/en-gb/library/ms190941(v=sql.105).aspx
17 People are following this question.
