A transport-level error has occurred when receiving results from the server

-> Application logs shows below error everyday intermittently between 12:00 AM to 12:10 AM,

Timestamp: 5/17/2020 10:05:58 PM
Message: HandlingInstanceID: c190c221-3e43-4702-83fa-533122cb3c81
An exception of type ‘System.Data.SqlClient.SqlException’ occurred and was caught.
———————————————————————————-
05/18/2020 00:05:58
Type : System.Data.SqlClient.SqlException, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – The semaphore timeout period has expired.)
Source : .Net SqlClient Data Provider
Help link :
Errors : System.Data.SqlClient.SqlErrorCollection
ClientConnectionId : c43b433e-4649-48f5-b0bf-05dca3d83432
Class : 20
LineNumber : 0
Number : 121
Procedure :
Server : JBDB01
State : 0
ErrorCode : -2146232060
Data : System.Collections.ListDictionaryInternal
TargetSite : Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action`1[System.Action])
HResult : -2146232060
Stack Trace : at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoLoadDataSet(IDbCommand command, DataSet dataSet, String[] tableNames)
at Microsoft.Practices.EnterpriseLibrary.Data.Database.LoadDataSet(DbCommand command, DataSet dataSet, String[] tableNames)
at Data.Export.GetNextCreatedExport()
at Business.Export.GetNextCreatedExport()
at Facade.Export.GetNextCreatedExport()
Additional Info:
MachineName : JBAPP01
TimeStamp : 5/17/2020 10:05:58 PM
FullName : Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=6.0.0.0, Culture=neutral, PublicKeyToken=daf167d5325e0d31
ThreadIdentity : System User
Inner Exception
—————
Type : System.ComponentModel.Win32Exception, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : The semaphore timeout period has expired
Source :
Help link :
NativeErrorCode : 121
ErrorCode : -2147467259
Data : System.Collections.ListDictionaryInternal
TargetSite :
HResult : -2147467259
Stack Trace : The stack trace is unavailable.
Category: Error
Priority: 2
EventId: 100
Severity: Error
Title:Enterprise Library Exception Handling
Machine: JBAPP01
Process Id: 6868
Win32 Thread Id: 11312
Thread Name:
Extended Properties: HelpLink.ProdName – Microsoft SQL Server
HelpLink.EvtSrc – MSSQLServer
HelpLink.EvtID – 121
HelpLink.BaseHelpUrl – http://go.microsoft.com/fwlink
HelpLink.LinkId – 20476

-> The error message at first glance seems like a network issue between Application and database server OR unresponsive database server due to high resource utilization.

-> I setup a perfmon collection on the database server and also started a continuous ping on Application server JBAPP01 pinging Database Server JBDB01 continuously using below powershell command,

ping.exe -t JBDB01|Foreach{"{0} - {1}" -f (Get-Date),$_} > C:\temp\ping\JBDB01.txt

-> The same issue happened. I checked the perfmon counters on the database server. Resource utilization was normal.

-> SQL Server error log and Event Viewer logs on database server did not have any errors during the time the error happened on the application server.

-> Checked the output of the ping command on Application Server and got below details,

5/18/2020 12:05:45 AM – Reply from 192.36.67.12: bytes=32 time<1ms TTL=127
5/18/2020 12:05:46 AM – Reply from 192.36.67.12: bytes=32 time<1ms TTL=127
5/18/2020 12:05:51 AM – Request timed out.
5/18/2020 12:05:56 AM – Request timed out.
5/18/2020 12:06:01 AM – Request timed out.
5/18/2020 12:06:06 AM – Request timed out.
5/18/2020 12:06:11 AM – Request timed out.
5/18/2020 12:06:12 AM – Reply from 192.36.67.12: bytes=32 time<1ms TTL=127
5/18/2020 12:06:13 AM – Reply from 192.36.67.12: bytes=32 time<1ms TTL=127

-> From the above output it is clear that the application is experiencing connectivity errors when there is packet loss as indicated by “Request Timed Out” from above output.

-> This was taken up with Network team. They worked with Windows team and advised that the packet loss are as a result of daily Server snapshot that was performed at that time.

-> Application team updated us that the functionality that is failing every day between 12:00 AM to 12:10 AM is some sort of health monitor that checks if all components of the Application including Database server is healthy. Since the error doesn’t have any business impact, they changed the schedule to skip between 12:00 AM to 12:15 AM and this resolved the issue.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

 

 

 

Rule “Valid DSN” and Rule “Valid Database compatibility level and successful connection” failed.

-> I was performing an In-Place upgrade from SQL Server 2014 to SQL Server 2016.

-> Below rules failed,

Blog91_1

-> Rule Check 1 -> Valid DSN

Blog91_2

—————————
Rule Check Result
—————————
Rule “Valid DSN” failed.

The report server configuration is not complete or is invalid. Use Reporting Services Configuration Manager to verify the report server configuration.
—————————
OK
—————————

-> Rule Check 2 -> Valid Database compatibility level and successful connection

Blog91_3
—————————
Rule Check Result
—————————
Rule “Valid Database compatibility level and successful connection” failed.

The report server database is not a supported compatibility level or a connection cannot be established. Use Reporting Services Configuration Manager to verify the report server configuration and SQL Server management tools to verify the compatibility level.
—————————
OK
—————————

-> SQL Services Reporting Services is installed, but not configured and it seems like thats the reason for this issue.

-> I configured SQL Services Reporting Services as below,

Blog91_4

Blog91_5

Blog91_6

Blog91_7

Blog91_8

Blog91_9

Blog91_10

Blog91_11

Blog91_12

-> Refresh the SQL Server 2014 rule window again and it will succeed this time,

Blog91_13

-> This allowed me to complete the upgrade without any further issue.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

 

Resizing SQL Server Database from single data file to multiple Data file

-> Requirement is to move data from a database that has 1 data file to 4 data files.

-> Existing setup,

SQL Server : SQL Server 2017
Database Size : 2 TB
Number of Data file(s) : 1
Data file size : 1.8 TB
Log file size : 200 GB

-> Solution requirement,

Number of Data files : 4
Data File 1 Size : 650 GB
Data File 2 Size : 650 GB
Data File 3 Size : 650 GB
Data File 4 Size : 650 GB
Log file size : 200 GB

-> Below tasks were undertaken on a test server initially.

-> Production database was restored on a test server. Additional 3 data drives of size 700 GB each added.

-> Database recovery model was changed from Full to simple.

-> Added close to 3 additional data files of size 650 GB on the 3 additional drives added.

-> Executed below command on the primary data file. This command basically moves data from all user objects from primary data file to additional data files that were added. This will result in all user objects to be moved from Primary data file to secondary data files added.

USE [DATABASE_NAME]
GO
DBCC SHRINKFILE (N'PRIMARY_DATA_FILE' , EMPTYFILE)
GO

-> The above command will be very slow. In my case it took close to 13 hours to complete. While the above command was executing I used below code to check the progress,


if convert(varchar(20),SERVERPROPERTY('productversion')) like '8%'
SELECT [name], fileid, filename, [size]/128.0 AS 'Total Size in MB',
[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB',
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB',
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/([size]/128.0))*100.0)) AS 'percentage Used'
FROM sysfiles
else
SELECT @@servername as 'ServerName',db_name() as DBName,[name], file_id, physical_name, [size]/128 AS
'Total Size in MB',
[size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB',
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Used Space In MB',
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/([size]/128.0))*100.0)) AS 'percentage Used'
FROM sys.database_files
go

-> Reviewing the output from above query, specifically “Used Space in MB” and “Percentage Used” will provide us the details whether the process is progressing.

-> I stopped the resizing query when the primary data file’s “Used space in MB” reached 471,860 MB.

-> I am stopped this in-between just to make sure I am not moving all data from primary data file and then resulting in too much new data being inserted to primary data file later.

-> Shrinked the primary data file from 1.8 TB to 650 GB.

-> There are instances where shrink can take several hours if resizing is not completed fully. In my case it completed in 3 minutes using below command,


USE [DATABASE_NAME]
GO
DBCC SHRINKFILE (N'PRIMARY_DATA_FILE' , 665600, TRUNCATEONLY)
GO

-> In case shrinking the primary data file is very slow, you should allow the resizing to complete fully. You will get below error message when it completes,

Msg 1119, Level 16, State 1, Line 20
Removing IAM page (3:5940460) failed because someone else is using the object that this IAM page belongs to.

-> You can get more details about above error message from this article.

-> Reissue the shrink command and it will complete soon.

-> Problem with this is that you will experience more writes on the primary data file than other 3 data files and this can result in sub-optimal performance.

-> Perform a reindex on the database to ensure you remove any fragmentation as a result of resizing.

-> Changed the recovery model for the database from Simple to Full and performed a full backup.

-> This method worked out well for me in the test environment.

-> This method was replicated on our production environment after 6 months. It had issues while performing on the production environment due to below reasons,

  1. The scripts to increase the data file in the production environment was a copy of the script used in Test environment.
  2. The data growth in production was not taken into account in 6 months and the additional data file size added did not cope up with the additional data added.

-> Due to above issue, behavior on Production database server was as below,

  1. When resizing was started and we were checking the progress using the above query provided. We found that Additional data files “Used Space in MB” was increasing, and “Available Space In MB” decreasing.
  2. But in primary data file “Used Space in MB” and “Available Space In MB” did not change, it was static. Expected result should be that “Available Space In MB” should be increasing and “Used Space in MB” should be decreasing.
  3. It was stopped after 10 hours. We then realized that after the shrink with empty file command was terminated, primary data file “Used Space in MB” started coming down and “Available Space In MB” increasing. This took 1 more hour and were able to see some data moved from Primary data file to secondary data file.
  4. We then increased the additional data file size appropriately and then started executing the command. It moved the required amount of data and it was working as expected. I stopped the resize at a value where data files were having same amount of data and performed an index optimize.

-> In my case I was lucky that we took downtime for a whole weekend.

-> The whole process will not be possible on a production environment in case there is not downtime allowed.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.