0

Need your suggestion on how below works.

Situation: We had unsent log on Principal stuck for almost 10 hours with unsent data around 200 GB.

Action: Paused and resumed the mirroring. This kicked of the stuck mirroring. All 200 GB is now send to mirror and its trying to restore. But when new transactions are showing up on principal i see that again the records or mirror seems to stuck showing send rate 0 KB/sec.

So my question would be , is this suppose to happen and block unsent log on P till all previous 200 GB is restored on Mirror or there could be different problem here? And can we again PAUSE and RESUME mirroring if mirroring is still synchronizing ?

Please suggest

5
  • are you using high safety or high performance mode? and what patch level of SQL Server are you in (SELECT @@VERSION)?
    – user37701
    Jan 8, 2018 at 1:49
  • high performance mode and Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 Jan 8, 2018 at 1:51
  • anything on SQL errorlog?
    – user37701
    Jan 8, 2018 at 1:54
  • nope, just mirror out of sync error Jan 8, 2018 at 2:30
  • What do you see when you run this for the database in question SELECT name, LOG_REUSE_WAIT_DESC FROM sys.databases ? Is your transaction log backup job working properly? Jan 8, 2018 at 2:30

1 Answer 1

0

So my question would be , is this suppose to happen and block unsent log on P till all previous 200 GB is restored on Mirror or there could be different problem here? And can we again PAUSE and RESUME mirroring if mirroring is still synchronizing ?

You might be experiencing a network issue. The PAUSE and RESUME of mirroring should just be your workaround.

Check with your network admin on your network bandwidth and latency (shared network with other apps? 1Mbps NIC?). This can build up your Unsent Log and can adversely impact performance of your database mirroring. 200GB is huge amount of data. You could loss 200GB data. You don't want that to happen. Consider setting up Threshold Alert for Log Send Queue KB. If it reaches 1GB, send an email alert and take action. This way you can minimize your data loss.

Read this KB Article on things to consider when setting up Database Mirroring in SQL Server. The 2 main important points about network:

To mitigate the impact of network latency on the database mirroring throughput, you should consider the following:

  • Gigabit network cards
  • Dedicated NICs for mirroring.

Don't wait for 10hrs or 200GB of unsent data, setup an alert and be proactive.

Additionally, consider migrating/upgrade to SQL Server 2016/2017. Use Availability Groups (better than mirroring and faster) because Database Mirroring is now deprecated by Microsoft and you won't be able to get support or updates from them (except you paid for extended support). If you run into Database Mirroring bug it is unlikely Microsoft will patch it for you. You can read the SQL Server 2008 lifecycle support here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.