SaveToSQLServer method error, Communication Link Failure

By WireINPUT -
Last week one of our team members was implementing a Change Control to create a Maintenance Plan on a SQL Server 2005 instance. Since this instance was getting backed up using a Third Party tool, it did not have any Maintenance Plans on it. Since we wanted to backup one of the databases to disk, this Change Control was put in place.

Everything went fine until we tried saving the Maintenance Plan. It could not be saved and popped up the following error message.

I came across this error message for the first time. My team members stumbled on this KB article which explains the cause for this error. Here is the summary of the symptoms and cause.
  • Maintenance Plans are saved as SSIS packages and stored in MSDB by default
  • These packages use encryption to connect to SQL Server.
  • Secure Network packets are sent over Secure Socket Layer (SSL) and Transport Layer Security(TLS) layers.
  • Data fragments sent over these layers have a limit of 16K.
  • If the packet size sent from SSIS is greater than 16K, then SSIS terminates it by throwing the “Communication Link Failure” exception.
This scenario occurs only when the Network Packet size sent from SQL Server is > 16K. The default Network packet size in SQL Server is 4096B (4K). This confirmed that on this instance, the Network Packet Size was modified manually. This value can be checked in the Server Properties in SSMS or by executing sp_configure. The Server Properties for this instance looked like this.

 
The Network Packet Size value was set to 32767 B (~32K). I have no clue when and why was this value set. When client applications send and receive lot of network packets, increasing the Network Packet Size helps in reducing the number of packets sent across the network. This in turn helps the application performance.

But I am of the opinion that any settings under sp_configure should only be changed after knowing the pros and cons and also after thorough testing. This article clearly explains the aspects that need to be taken into consideration before changing the Network Packet Size value.

Moral of the story: Don’t change the settings on the fly. Take your time for analysis. At the end of the it is the instance that you support and you never know when the surprise package arrives!
 

Comments

Post a Comment

Popular posts from this blog

VMware PSOD Purple Screen of Death - Debugger waiting (world 2078) -- no port for remote debugger. "Escape" for local debugger

By WireINPUT -
Image
How to deal with PSOD - VMware PSOD Purple Screen of Death - Debugger waiting (world 2078) -- no port for remote debugger. "Escape" for local debugger - Easy troubleshooting steps and how to identify or determine exact root cause of the PSOD issue Host is down with PSOD with the above said error. Verified BIOS settings, worked with on site engineer, contacted the IBM server support (since this is IBM server, have contacted IBM, if it is IBM or some other hardware, you may need to reach out the respective vendor) Sometimes it could be server hardware issue like DIMMs, CPU, Processor related or may be a chance of network related. Please have a through check on the above said and come to a conclusion what could the issue. Based on that, need to troubleshoot the issue.
Read more

The Windows Time Service terminated with the following error - Event ID 7023 & 46

By nag -
After you upgrade a Microsoft Windows Server 2003-based domain controller to Windows Server 2003 Service Pack 1 (SP1), the Windows Time service may not start. In this scenario, the following events may be logged in the Windows System log. Message 1 Event Type: Error Event Source: Service Control Manager Event Category: None Event ID: 7023 Description: The Windows Time service terminated with the following error: Not all privileges referenced are assigned to the caller. For more information, see Help and Support Center at http://support.microsoft.com. Message 2 Event Type: Error Event Source: W32Time Event Category: None Event ID: 46 Description: The time service encountered an error and was forced to shut down. The error was: 0x80070700: An attempt was made to logon, but the network logon service was not started. Additionally, when you try to start the Windows Time service manually, you may receive one of the following error messages: Error 1083: The executable program that this servic
Read more

IBM x3650 M4 Series Server Model - Activation Keys Backup to be taken for IMM Moduel II, why?

By WireINPUT -
Image
If you are planning to replace the system board (Mother Board) for M4 series of IBM xSeries 3650 model server, you should and must take the activation keys backup, otherwise you will miss the IMM key. IMM key something like iLO Activation key on HP servers. If that  iLO key didn't acivated, then you won't get the 'Remote Access' option. In the same way, IBM series requires a key to be activated to access the IMM console. IMM Activation to be backuped and restore once the new board get replaced. Here is the screenshot there you see more options what we discussed so far -
Read more