question

SQLDBASOON avatar image
SQLDBASOON 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.
sql server 2008 r2mirroringdatabase-mirroring
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
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.
0 Likes 0 ·
So you're not doing regular transaction log backups?
1 Like 1 ·
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 .
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
2 Likes 2 ·
Fatherjack avatar image
Fatherjack answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.