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!