locked
SQL Server Transaction Times Out when DB Is Mirrored RRS feed

  • Question

  • I'm getting time out errors on the clients in a High protection Mirror setup.  I've got two Windows 2003 R2 SP2 servers running SQL Server 2005 both updated to latest patch level in SQL.  When the clients have transactions that are a little larger than usual "around 50-100", they time out and can't resend.  These same clients sending the same transactions worked great before the Mirror was setup.  There's plenty of bandwidth on the Lan between clients and the Principal server as well as between the Principal to the Mirror server.  Weird thing is, the transactions that hang, actually show up in the database.  Can anyone please give me some direction into what I can do to get this working?.. or point me in the right direction.

    Thanks,

    RDSR2k
    Friday, October 24, 2008 8:34 PM

All replies

  • Time-outs normally scream table-locks typically sourced to explicit transactions.

     

    A.D.T.

     

    Friday, October 24, 2008 8:36 PM
  • Can you check out if the is_send_flow_controlled and is_receive_flow_controlled columns in the sys.dm_db_mirroring_connections DMV (on both mirror and principal) ? Also, how are the disk performance counters doing on the mirror? Is the 'Physical Disk/Avg. Disk Queue Lenght' high? The symptomps you describe would indicate not necessarily a LAN bandwith bottleneck, but a log hardening on the mirror bottleneck (ie. the mirror cannot write fast enough the received log to the disk).
    Saturday, October 25, 2008 8:05 AM
  • Thanks for the help Remus,

     

    How do I query that information you spoke about.... is_receive_flow_controlled, is_send_flow_controlled, and that sys.dm_db_mirroring_connections DMV?  I'm not familiar with these commands or what they actually do... could you help me figure out how to interpret this information?  Any help is much appreciated!!

     

    Thanks,

    RDSR2k

    Wednesday, October 29, 2008 7:47 PM
  • Hi RDSR2k,

    sys.dm_db_mirroring_connections is a database management view and you can run queries against them.  They are views that call the underlying tables that SQL server uses to manage an instance. 

    You can run:

    select is_receive_flow_controlled, is_send_flow_controlled from sys.dm_db_mirroring_connections

    on both you
    principal and mirrored databases to return the info. 

    More info on the above DMV can be found here:


    http://msdn.microsoft.com/en-us/library/ms189796(SQL.90).aspx

    An intro to DMV's can be found here:

    http://msdn.microsoft.com/en-us/library/ms188754(SQL.90).aspx

    The other tool Remus is talking about is Performance Monitor.  It is a tool to monitor the server and is installed as part of your OS.  It can be accessed by going to Start -> Run -> perfmon.exe

    You can monitor certain parts of you system by adding counters to monitor. 

    Books online is a great source of information to find further details on any of this information and more.

    Hope this helps a bit


    Thursday, October 30, 2008 10:58 AM
  • Thanks Steve... Very much appreciate all the help from all.  I'm coming into this with little knowledge of the internal tools available in SQL.  I've got extensive experience at the implementation of SQL and a working knowledge in regard to implementation of replication and Mirroring setup, but still learning the tools.  Again, thanks and will take a look at both of those articles and let know know what I found out.

     

    Thanks,

     

    RDSR2k

    Thursday, October 30, 2008 9:56 PM
  • Looks like we are getting closer to the issue... we are having issues with time outs occuring.  We extended the transaction time out to "0" or infinite and it didn't have any affect on our issue.  We did find out that the commit is actually the piece that is timing out with the error "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."  All the other transactions that are smaller commit just fine... it's the default time out that it is set on to commit that is our issue.  Can anyone tell me how to change the default "commit" time out value?  I've seen a couple of webpages that talk about it being able to be manipulated, but not one that tells how to do it.  Again, any help is appreciated.


    Thanks,
    RDSR2k
    Monday, November 3, 2008 8:07 PM