Unexpected server behavior with SQL Server

Dear,

Production server inexplicably responds. Server runs on Windows Server 2012 Hyper-V. There is also identical to the DR server. On Server stores the production base, and is configured and Mirror Database to DR server. On the server noted that the Mirror broke the period of an hour. I could not connect to the SQL Server Configuration Manager, the error occurred:

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.

Invalid class [0x8004100]

I started CMD set in the folder C:\Program Files <x86>\Microsoft SQL Server\110\Shared and typed command mofcomp sqlmgmproviderxpsp2up.mof

After that I started the SQL Server Configuration Manager. When connecting to SQL server error occurred:

Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=4; handshake=14995

I did the following: In SQL Server Configuration Manager, the SQL Server Network Configuration, TCP / IP tab in the IP Addresses I included setting Enabled to Yes on all addresses from my network. In addition to the network settings of the network card, I had to turn on Internet Protocol Version 6 (TCP / IPv6). After I restarted the server could access the database. In addition, I had the Windows Firewall to let port 5022, which is used for the Mirror. In addition to the SQL Error log I noticed that constantly reporting the error:

There have been 2510xxxx misaligned log IOs which required falling back to synchronous IO.  The current IO is on file x:\x\x\x_x.ldf.

Also when connecting to Mirror the error occurred:

Error: 1479, Severity: 16, State: 1.

 

Now everything is operating normally, but I'm interested in why this happened?

Any idea what could be the reason?

 

Thank you


  • Moved by BrianEhMVP Thursday, September 10, 2015 2:52 PM
  • Edited by Maty M Friday, September 11, 2015 5:52 AM
September 10th, 2015 9:08am

Normally on mirror scenario we see Error: 1479, Severity: 16, State: 1. and mostly it is error 64 which means specified network name not available. Please see this for that info http://support.microsoft.com/kb/942861

Regarding There have been 2510xxxx misaligned log IOs which required falling back to synchronous IO.  The current IO is on file x:\x\x\x_x.ldf.

please see this post http://www.sqlservercentral.com/articles/misaligned+log+IO/76684/

Free Windows Admin Tool Kit Click here and download it now
September 11th, 2015 1:29pm

The permission error you see here is something I have encountered in the past only when the server could not connect to the Active Directory controller. That would be consistent to your Pre-Login-Handshake issue, but it is hard to prove unfortunately.

The misaligned IO happens when your blocksizes don't match up, at least that's my experience. This can lead to massive performance degredation, but I have never seen a disconnect so far because of it.

September 11th, 2015 5:21pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics