Sql Server Error Message : Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library

Quick one today:

Recently, we noticed an interesting error in our production system. Following are the steps we took to uncover the details of this error entry.

Error Message:

Error: 17836, Severity: 20, State: 17.
Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: a.b.c.d]

Step 1:

First we need to figure out where is this database connection request coming from. Use DNS look up (nslookup a.b.c.d) command to identify the machine that is sending this connection.

Step 2:

Similarly, use Network command netstat -anp to uncover all the connections coming into the database server; Then filter them down to this particular IP (a.b.c.d) and see what port are they originating from.

Step 3:

Now go to that remote machine and run similar netstat -anp command on it. Now, compare and confirm that the PID for this process.

Step 4:

With the above two steps, you could find out exactly what software in that remote machine is trying to connect to the database server.

Now that you have the exact PID (ProcessID) on the remote machine, go to Task Manager and check under Processes tab to see the originating software name.

Step 5:

See if using Sql Authentication could help in allowing that remote machine to connect successfully (if its a valid login request).

There are some other unconventional approaches to digging in further, but the above steps should be sufficient in troubleshooting.

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server Error Message: spid x I/O is frozen on database xxxx. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

Quick one today:

Rarely, but every once in a while, we see this entry in our production virtual database servers. Initially it seems benign, but upon further investigation we learnt that this is a repeating issue and there is an explanation for it.

Error Message:

spid125     I/O is frozen on database master. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup

The same message could appear for all databases on this drive.

Explanation:

In short, this occurs when VSS backups are made through VDI.

Long Story:

Sql Server has a lot of APIs that enable independent software vendors to integrate Sql Server into their own products. Through these APIs, they carry out necessary functionality while maintaining the reliability, feature-functionality and performance of Sql Server. One such API interface is VDI (Virtual Device Interface); This works from within the VSS (Volume Shadow copy Service), through which 3rd party tools could perform backup & restore operations through BACKUPs using SNAPSHOT.

Internals of VSS in Sql Server (from Microsoft blog)

Using the VDI interface, when such command (BACKUP WITH SNAPSHOT) is executed, Sql Server needs to make sure that the backups are reliable and complete. Since data files are under constant lock by Sql Server with continuous updates going on, Sql Server has to temporarily cease all I/O into the data files to secure a volume snapshot.

Once that is complete, the same 3rd party tool tells Sql Server through VDI that I/O can now resume. This is usually for a short duration, but depending on the size of the file and the throughput of the drive, it could sometimes take longer.

In our environment, we see I/O freeze for a few seconds. This is not good. So we schedule these snapshot backups during daily scheduled down times.

2016-02-19 18:36:36.37 spid125 I/O is frozen on database master. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
2016-02-19 18:36:47.91 spid125 I/O was resumed on database master. No user action is required.

Since this is internal Sql Server functionality that Microsoft controls, looks like they are working towards making it more efficient in future releases. Some Microsoft tools that rely on this are:

• Microsoft Backup (ntbackup.exe)
• Volume Shadow Copy (VSS)
• Data Protection Manager (DPM)

Some 3rd party tools that use VSS to perform such backup and restore operations are:

• Symantec Business Continuance Volume
• Veritas Net Backup

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : How to enable or disable FOREIGN KEY and CHECK KEY constraints

Quick one today:

Sql Server provides interesting tools to play with many great features is has. One such feature is CONSTRAINTS. You could add FOREIGN KEY, CHECK, PRIMARY KEY, UNIQUE KEY, NOT NULL, etc. Some of these could be disabled as needed without losing the CONSTRAINT definition on the table.

For example, lets take Parent & Child tables. Child table has a FOREIGN KEY constraint to the Parent table. See below:

--
-- Create Parent & Child tables
--
CREATE TABLE dbo.Parent (
     Parent_ID    INT    NOT NULL     CONSTRAINT    PK_Parent_Id                   PRIMARY KEY                      CLUSTERED
   , Name         BIT    NULL
)
GO

CREATE TABLE dbo.Child (
     Child_Id     INT    NOT NULL     CONSTRAINT    PK_Child_Id                   PRIMARY KEY                        CLUSTERED
   , Parent_ID    INT    NOT NULL     CONSTRAINT    FK_Child_Parent_Parent_ID     REFERENCES dbo.Parent(Parent_ID)
)
GO

In the above example, we create foreign key constraint between Parent & Child table on Parent_Id column in Child table referencing Parent_Id in Parent table. So any records entered into Child table, must comply with the rules of the constraint a.k.a. for every record INSERTed, internal checks are performed for validity of data integrity with the data model.

If it fails, you’ll get an error like this:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Child_Parent_Parent_ID". The conflict occurred in database "tempdb", table "dbo.Parent", column 'Parent_ID'.
The statement has been terminated.

Disable CONSTRAINT

Sometimes, in lab environment, we want to disable such constraints. There is an easier way to disable without DROPping them. The key word NOCHECK tells Sql Server not to check for compliance, while still keeping the constraint definition in catalog.

--
-- Disable constraint, without dropping it
--
ALTER TABLE dbo.Child NOCHECK CONSTRAINT FK_Child_Parent_Parent_Id
GO

When the constraint is disabled, any records entered into Child table are not checked for compliance with the constraint. So, INSERTing un-compliant data will still be successful.

(1 row(s) affected)

Enable CONSTRAINT

Now to enable it, we use the following command.

--
-- Disable constraint, without dropping it
--
ALTER TABLE dbo.Child CHECK CONSTRAINT FK_Child_Parent_Parent_Id
GO

All Constraints at the same time

If you want to disable or enable all constraint on the table at the same time, use the commands below.

--
-- Disable all constraints on a table (without dropping them)
--
ALTER TABLE dbo.Child NOCHECK CONSTRAINT ALL
GO

--
-- Enable all constraints on a table
--
ALTER TABLE dbo.Child CHECK CONSTRAINT ALL
GO

 

Side Note:

The key word, NOCHECK could be used in two ways.

• One is to disable, like above
• The other is in constraint definition. If you use NOCHECK during constraint definition, it tells Sql Server to enforce the constraint going forward, but not for existing records. So any data that does not comply with the new constraint are not checked, but any new records are checked for compliance. This is really interesting nuance.

 

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server : How to read Sql Server Error Log into a table

Quick one today:

Sometimes when working on some issues, it becomes necessary to review error message entries in Sql Server Error Log file. On occassions, when there are too many entries and it becomes necessary to find patterns. Manually finding patterns is tedious.

Sql Server Error Log is a text file located under Log file under Sql Server installation location. When looking for patterns, it helps to read Sql Server Error Log into a table format; It gets easier to run analytical queries this way. Luckily Sql Server has a mechanism for this:

--
-- Read Error Log text file into table format
--
IF OBJECT_ID('tempdb..#ErrorLog') IS NOT NULL
     DROP TABLE #errlog
GO

CREATE TABLE #ErrorLog
(
     ID			INT      NOT NULL      PRIMARY KEY       CLUSTERED
   , LogDate		DATETIME
   , ProcessInfo	VARCHAR(1000)
   , [Text]		VARCHAR(8000)
)
GO

INSERT INTO #ErrorLog (LogDate, ProcessInfo, [Text])
EXEC sp_readerrorlog
GO

SELECT * 
FROM #ErrorLog
GO

With more parameters:

Using the same undocumented stored procedure procedure, we could retrieve older Error log files and Sql Server Agent log files. We do this using its parameters:

sp_readErrorLog <Log Number>, <Type>, <Search String>

• <Log Number> indicates if we want to retrieve the current (0) or older files (1, 2, 3, ….n) .  Ex: ERRORLOG, ERRORLOG.1, ERRORLOG.2, ERRORLOG.3, etc
• <Type> indicates Sql Server Error Log file (1) or Agent Log File (2)
• <Search String> represents the sample text you want to filter

Sql Server Error Log

Examples for Sql Server Error Log:

1. To retrieve ‘ERRORLOG.1’ (archive 1) from Sql Server Error Log Files
   1. EXEC sp_readerrorlog 1, 1
2. To retrieve ERRORLOG.2 from Sql Server Error Log files and filter for a string ‘Error’
   1. EXEC sp_readerrorlog 2, 1, ‘Error’

Examples for Sql Server Agent Error Log:

1. To retrieve current Sql Server Agent Error Log file
   1. EXEC sp_readerrorlog 0, 2

 

Hope this helps,

_Sqltimes

Read Full Post »