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.
SELECT @@VERSION)?SELECT name, LOG_REUSE_WAIT_DESC FROM sys.databases? Is your transaction log backup job working properly?