Timeout expire on updating nvarchar(max)

Question

I have a table which have nvarchar(Max) field and it contains character approximately 50,0000 per record. When i insert data, it is OK but when update the data then Error occurred : "Timeout expired The timeout period elapsed prior to compilation of the operation or the server is not responding "

CREATE TABLE [dbo].[tblJudgementText]( [JudgementID] [int] NOT NULL, 
  [JudgementText] [nvarchar](max) NULL, 
  [Source] [nvarchar](500) NULL, 
  [UserID] [int] NULL, 
  [ModifiedDate] [datetime] NULL, 
  CONSTRAINT [PK_tblJudgementText] PRIMARY KEY CLUSTERED ( [JudgementID] ASC))

i also try for connection timeout to = 100

2nd way i tried delete first record and insert new one but for deleting recored it throw same error

You must provide select statement, maybe some table definition. — Mad Dog Tannen, Oct 02 '13 at 07:23
might it would take more time,you can increase you timeout period. — Anand, Oct 02 '13 at 07:25

score 0 · Answer 1 · answered Oct 02 '13 at 07:34

SQL Server does not throw time out errors normally. It seems like the error is thrown by the client, whatever client API you use. Some client APIs, eg. the managed SqlClient, set a default request timeout of 30 seconds, see SqlCommand.CommandTimeout , and after this time passes they abort the request.

So in truth it appears that you have a performance problem, your UPDATE statement is taking more than 30 seconds probably. We cannot guess what the problem is, only you have the information what is happening. Approach this as a performance problem, read Waits and Queues and try to apply its methodology. Read Understanding how SQL Server executes a query to know what to look for.

My physic powers tell me that your UPDATE WHERE clause contains a non-sargable condition that results in a scan. This either blocks on locks or just times out on IO, even my pshyhic powers cannot figure this one out w/o any info...

Post the extact table definition, the exact UPDATE statement and the execution plan (the XML!, not a picture of it).

score 0 · Answer 2 · answered Oct 02 '13 at 07:38

0

Are you updating from the SQL server client or doing it programatically..? Try to give 'select .. for update' before giving the update instruction.

If there is no other constraints, you can even think of deleting the record and insert as a new record.

answered Oct 02 '13 at 07:38

Nicky Jaidev

447
1
4
12

update using stored procedure i tried first delete record and inserting new one but for deleting it throw same error – user2837615 Oct 02 '13 at 08:17
seems you may have to rewrite the code/query so that you delete one record at a time and then do the insert. Probably you might be deleting large number of records in a single query. If you could post the snippets from the stored procedure, will be great. – Nicky Jaidev Nov 11 '13 at 17:34

Timeout expire on updating nvarchar(max)

2 Answers2