locked
Root cause for logins were timed out in Asychronous mirroring RRS feed

  • Question

  • We have SQL Server 2008 Sp3 64 bit server with asynchronous mirroring on two databases. The mirroring was active and inactive continuously for more than 1 hour and suddenly the logins were timed out on principal server.

    So now we are trying to find the root cause.

    Any help.

    2014-12-10 14:51:07.880 spid17s Database mirroring is active with database 'opale' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 14:52:04.310 spid17s Database mirroring is inactive for database 'opale'. This is an informational message only. No user action is required.
    2014-12-10 14:52:55.650 spid17s Database mirroring is active with database 'opale' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 14:52:57.760 spid17s Database mirroring is inactive for database 'opale'. This is an informational message only. No user action is required.
    2014-12-10 14:54:19.820 spid17s Database mirroring is active with database 'opale' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 14:54:24.550 spid17s Database mirroring is inactive for database 'opale'. This is an informational message only. No user action is required.
    2014-12-10 14:54:47.590 spid17s Database mirroring is active with database 'opale' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 14:55:22.070 Backup Log was backed up. Database: model, creation date(time): 2003/04/08(09:13:36), first LSN: 134:488:1, last LSN: 134:488:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'TDPSQL-0000109C-0000'}). This is an informational message only. No user action is required.
    2014-12-10 14:55:42.520 Backup Log was backed up. Database: opale, creation date(time): 2010/03/06(08:36:00), first LSN: 3689101:41595:1, last LSN: 3689101:56840:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'TDPSQL-0000109C-0000'}). This is an informational message only. No user action is required.
    2014-12-10 14:55:59.680 Backup Log was backed up. Database: opaleGmao, creation date(time): 2010/06/29(16:37:36), first LSN: 2925:158:1, last LSN: 2925:160:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'TDPSQL-0000109C-0000'}). This is an informational message only. No user action is required.
    2014-12-10 14:56:44.210 spid17s Database mirroring is inactive for database 'opale'. This is an informational message only. No user action is required.
    2014-12-10 14:57:15.630 spid17s Database mirroring is active with database 'opale' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 14:58:21.590 spid17s Database mirroring is inactive for database 'opale'. This is an informational message only. No user action is required.
    2014-12-10 14:58:26.690 spid17s Database mirroring is active with database 'opale' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 14:59:13.700 spid18s Database mirroring is inactive for database 'opaleGmao'. This is an informational message only. No user action is required.
    2014-12-10 14:59:14.360 spid18s Database mirroring is active with database 'opaleGmao' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 15:02:57.610 spid18s Database mirroring is inactive for database 'opaleGmao'. This is an informational message only. No user action is required.
    2014-12-10 15:03:00.790 spid18s Database mirroring is active with database 'opaleGmao' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 15:05:12.290 spid18s Error: 1479, Severity: 16, State: 1.
    2014-12-10 15:05:12.290 spid18s The mirroring connection to "TCP://SMSNDCOPALSQLP2.eua.bvcorp.corp:5022" has timed out for database "opaleGmao" after 10 seconds without a response.  Check the service and network connections.
    2014-12-10 15:05:12.290 spid18s Database mirroring is inactive for database 'opaleGmao'. This is an informational message only. No user action is required.
    2014-12-10 15:05:20.880 spid17s Error: 1479, Severity: 16, State: 1.
    2014-12-10 15:05:20.880 spid17s The mirroring connection to "TCP://SMSNDCOPALSQLP2.eua.bvcorp.corp:5022" has timed out for database "opale" after 10 seconds without a response.  Check the service and network connections.
    2014-12-10 15:05:20.880 spid17s Database mirroring is inactive for database 'opale'. This is an informational message only. No user action is required.
    2014-12-10 15:05:27.160 spid18s Database mirroring is active with database 'opaleGmao' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 15:05:28.990 spid17s Database mirroring is active with database 'opale' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 15:06:34.870 Backup Log was backed up. Database: model, creation date(time): 2003/04/08(09:13:36), first LSN: 134:488:1, last LSN: 134:488:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'TDPSQL-00000954-0000'}). This is an informational message only. No user action is required.
    2014-12-10 15:06:46.360 Backup Log was backed up. Database: opale, creation date(time): 2010/03/06(08:36:00), first LSN: 3689101:56840:1, last LSN: 3689101:59770:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'TDPSQL-00000954-0000'}). This is an informational message only. No user action is required.
    2014-12-10 15:07:00.970 Backup Log was backed up. Database: opaleGmao, creation date(time): 2010/06/29(16:37:36), first LSN: 2925:160:1, last LSN: 2925:162:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'TDPSQL-00000954-0000'}). This is an informational message only. No user action is required.
    2014-12-10 15:08:46.420 spid17s Database mirroring is inactive for database 'opale'. This is an informational message only. No user action is required.
    2014-12-10 15:09:10.970 spid270 Error: 18056, Severity: 20, State: 29.
    2014-12-10 15:09:10.970 spid270 The client was unable to reuse a session with SPID 270, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
    2014-12-10 15:09:14.150 spid17s Database mirroring is active with database 'opale' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 15:10:00.480 spid17s Database mirroring is inactive for database 'opale'. This is an informational message only. No user action is required.
    2014-12-10 15:10:54.230 spid18s Database mirroring is inactive for database 'opaleGmao'. This is an informational message only. No user action is required.
    2014-12-10 15:11:21.600 spid18s Database mirroring is active with database 'opaleGmao' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 15:12:20.080 spid18s Error: 1479, Severity: 16, State: 1.
    2014-12-10 15:12:20.080 spid18s The mirroring connection to "TCP://SMSNDCOPALSQLP2.eua.bvcorp.corp:5022" has timed out for database "opaleGmao" after 10 seconds without a response.  Check the service and network connections.
    2014-12-10 15:14:46.840 spid18s Database mirroring is inactive for database 'opaleGmao'. This is an informational message only. No user action is required.
    2014-12-10 15:15:20.320 spid18s Error: 1479, Severity: 16, State: 1.
    2014-12-10 15:15:20.320 spid18s The mirroring connection to "TCP://SMSNDCOPALSQLP2.eua.bvcorp.corp:5022" has timed out for database "opaleGmao" after 10 seconds without a response.  Check the service and network connections.
    2014-12-10 15:17:42.800 spid18s Database mirroring is active with database 'opaleGmao' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 15:19:31.640 spid18s Database mirroring is inactive for database 'opaleGmao'. This is an informational message only. No user action is required.
    2014-12-10 15:20:14.340 spid18s Database mirroring is active with database 'opaleGmao' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 15:20:41.920 spid18s Error: 1479, Severity: 16, State: 1.
    2014-12-10 15:20:41.920 spid18s The mirroring connection to "TCP://SMSNDCOPALSQLP2.eua.bvcorp.corp:5022" has timed out for database "opaleGmao" after 10 seconds without a response.  Check the service and network connections.
    2014-12-10 15:20:48.890 spid17s Error: 1479, Severity: 16, State: 1.
    2014-12-10 15:20:48.890 spid17s The mirroring connection to "TCP://SMSNDCOPALSQLP2.eua.bvcorp.corp:5022" has timed out for database "opale" after 10 seconds without a response.  Check the service and network connections.
    2014-12-10 15:21:49.320 spid18s Database mirroring is inactive for database 'opaleGmao'. This is an informational message only. No user action is required.
    2014-12-10 15:22:02.610 spid18s Database mirroring is active with database 'opaleGmao' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 15:22:33.170 Backup Log was backed up. Database: model, creation date(time): 2003/04/08(09:13:36), first LSN: 134:488:1, last LSN: 134:488:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'TDPSQL-000016F0-0000'}). This is an informational message only. No user action is required.
    2014-12-10 15:23:06.950 Backup Log was backed up. Database: opale, creation date(time): 2010/03/06(08:36:00), first LSN: 3689101:59770:1, last LSN: 3689101:77104:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'TDPSQL-000016F0-0000'}). This is an informational message only. No user action is required.
    2014-12-10 15:23:23.960 Backup Log was backed up. Database: opaleGmao, creation date(time): 2010/06/29(16:37:36), first LSN: 2925:162:1, last LSN: 2925:164:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'TDPSQL-000016F0-0000'}). This is an informational message only. No user action is required.
    2014-12-10 15:23:29.140 spid18s Error: 1479, Severity: 16, State: 1.
    2014-12-10 15:23:29.140 spid18s The mirroring connection to "TCP://SMSNDCOPALSQLP2.eua.bvcorp.corp:5022" has timed out for database "opaleGmao" after 10 seconds without a response.  Check the service and network connections.
    2014-12-10 15:23:37.940 spid18s Database mirroring is inactive for database 'opaleGmao'. This is an informational message only. No user action is required.
    2014-12-10 15:24:29.800 spid18s Database mirroring is active with database 'opaleGmao' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 15:26:18.620 spid18s Database mirroring is inactive for database 'opaleGmao'. This is an informational message only. No user action is required.
    2014-12-10 15:26:29.890 spid18s Error: 1479, Severity: 16, State: 1.
    2014-12-10 15:26:29.890 spid18s The mirroring connection to "TCP://SMSNDCOPALSQLP2.eua.bvcorp.corp:5022" has timed out for database "opaleGmao" after 10 seconds without a response.  Check the service and network connections.
    2014-12-10 15:27:14.050 spid18s Database mirroring is active with database 'opaleGmao' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 15:28:45.470 spid18s Database mirroring is inactive for database 'opaleGmao'. This is an informational message only. No user action is required.
    2014-12-10 15:31:49.750 spid18s Database mirroring is active with database 'opaleGmao' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 15:31:49.760 spid18s Database mirroring is inactive for database 'opaleGmao'. This is an informational message only. No user action is required.
    2014-12-10 15:32:50.710 spid18s Database mirroring is active with database 'opaleGmao' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 15:36:38.300 spid17s Error: 1479, Severity: 16, State: 1.
    2014-12-10 15:36:38.300 spid17s The mirroring connection to "TCP://SMSNDCOPALSQLP2.eua.bvcorp.corp:5022" has timed out for database "opale" after 10 seconds without a response.  Check the service and network connections.
    2014-12-10 15:36:41.770 spid18s Error: 1479, Severity: 16, State: 1.
    2014-12-10 15:36:41.770 spid18s The mirroring connection to "TCP://SMSNDCOPALSQLP2.eua.bvcorp.corp:5022" has timed out for database "opaleGmao" after 10 seconds without a response.  Check the service and network connections.
    2014-12-10 15:36:41.780 spid18s Database mirroring is inactive for database 'opaleGmao'. This is an informational message only. No user action is required.
    2014-12-10 15:38:04.570 spid18s Database mirroring is active with database 'opaleGmao' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 15:39:30.040 spid17s Database mirroring is active with database 'opale' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 15:39:30.060 spid17s Database mirroring is inactive for database 'opale'. This is an informational message only. No user action is required.
    2014-12-10 15:39:32.280 spid17s Database mirroring is active with database 'opale' as the principal copy. This is an informational message only. No user action is required.
    2014-12-10 15:40:03.970 Backup Log was backed up. Database: model, creation date(time): 2003/04/08(09:13:36), first LSN: 134:488:1, last LSN: 134:488:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'TDPSQL-00002190-0000'}). This is an informational message only. No user action is required.


    Thanks

    • Moved by Olaf HelperMVP Thursday, December 11, 2014 12:06 PM Moved from "Database Engine" to a more related forum
    Thursday, December 11, 2014 11:55 AM

Answers


  • If the Mirrorred instance doesnt not receive any responses to Ping for 10 seconds from Principal and mirror server.


    ALTER DATABASE <dbname> SET PARTNER TIMEOUT 60

    Visit link it might help Mirroring time out

    Mirroing best practice Click Here

    Thanks






    Thursday, December 11, 2014 12:01 PM
  • Hi Ajay,

    its seems you have configured mirroring in a environment where huge network latency exist and connection break-up very frequently as per SequelMate  you can increase partner time out till the idle value achieved, login failure alert should occurred on principal server because principal database will be accessible during such way.

    Regards,

    Pawan Singh 


    Thanks

    Thursday, December 11, 2014 12:17 PM
  • Hi Ajay,

    As your description, you come across the login timeout issue in database mirroring. This issue could occur due to the network errors, such as TCP link time-outs, packet errors. Besides the link (Mirroring time out) from SequelMate, I recommend you to check if there is any packet loss or errors in your network connection.

    Before detecting packet errors, you could enable a hidden setting in Windows to show you any possible errors with your connection and make a quick registry change as the following steps.

    1. Press Win key+R to bring up the Run box and type regedit to launch the Windows registry editor.

    2. Navigate to the following registry location:

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Network\Connections\StatMon

    If the StatMon key is not there, right click on Connections, select New -> Key and enter the value as StatMon.

    3. Inside the StatMon key, set the ShowLanErrors DWORD value as 1 if it’s already there. If ShowLanErrors is not there, right click on StatMon and select New -> DWORD value. Set the DWORD value name as ShowLanErrors. Double click on ShowLanErrors and set the value data as 1.

    4. Open Network and Sharing Center >> Click Change adapter settings >> Right click your network connection >> Select Status. You will now see an additional Errors information in your connection status below the Sent and Received Bytes. For more information about the process, please refer to the article: https://www.raymond.cc/blog/detect-packet-errors-in-your-network-lan-connection-status/.

    If you find packet errors, you could fix the issue according the article: http://www.ehow.com/how_8546105_fix-loss-packet-error.html

    Regards,
    Michelle Li

    Friday, December 12, 2014 11:45 AM

All replies


  • If the Mirrorred instance doesnt not receive any responses to Ping for 10 seconds from Principal and mirror server.


    ALTER DATABASE <dbname> SET PARTNER TIMEOUT 60

    Visit link it might help Mirroring time out

    Mirroing best practice Click Here

    Thanks






    Thursday, December 11, 2014 12:01 PM
  • I would not do what SequelMate told I would find out cause why it was not able to ping was it some network issue ? Making time out to 60 is really a bad idea unless you have strong reason for that

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Thursday, December 11, 2014 12:11 PM
  • Hi Ajay,

    its seems you have configured mirroring in a environment where huge network latency exist and connection break-up very frequently as per SequelMate  you can increase partner time out till the idle value achieved, login failure alert should occurred on principal server because principal database will be accessible during such way.

    Regards,

    Pawan Singh 


    Thanks

    Thursday, December 11, 2014 12:17 PM
  • Hi Ajay,

    As your description, you come across the login timeout issue in database mirroring. This issue could occur due to the network errors, such as TCP link time-outs, packet errors. Besides the link (Mirroring time out) from SequelMate, I recommend you to check if there is any packet loss or errors in your network connection.

    Before detecting packet errors, you could enable a hidden setting in Windows to show you any possible errors with your connection and make a quick registry change as the following steps.

    1. Press Win key+R to bring up the Run box and type regedit to launch the Windows registry editor.

    2. Navigate to the following registry location:

    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Network\Connections\StatMon

    If the StatMon key is not there, right click on Connections, select New -> Key and enter the value as StatMon.

    3. Inside the StatMon key, set the ShowLanErrors DWORD value as 1 if it’s already there. If ShowLanErrors is not there, right click on StatMon and select New -> DWORD value. Set the DWORD value name as ShowLanErrors. Double click on ShowLanErrors and set the value data as 1.

    4. Open Network and Sharing Center >> Click Change adapter settings >> Right click your network connection >> Select Status. You will now see an additional Errors information in your connection status below the Sent and Received Bytes. For more information about the process, please refer to the article: https://www.raymond.cc/blog/detect-packet-errors-in-your-network-lan-connection-status/.

    If you find packet errors, you could fix the issue according the article: http://www.ehow.com/how_8546105_fix-loss-packet-error.html

    Regards,
    Michelle Li

    Friday, December 12, 2014 11:45 AM