CODEWITHSUNDEEP
sqltimeout
Fiona
Fiona

Reputation: 1599

SQL Server 2008 - SQL timeout exception on Insert Linq query

Currently my windows service is processing approximately 1500 transacations a day. About once a week I get a random timeout exception on an Insert done using LINQ.

The exception is:

Exception (SqlException) System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

The Linq query is:

dc.TransactionLoggings.InsertOnSubmit(new TransactionLogging()
                    {
                        DateAdded = DateTime.UtcNow,
                        InputMessage = message,
                        DocId = documentID.ToString(),
                        TransactionStatus = transactionStatus
                    });
                    dc.SubmitChanges();

Any ideas/suggestions on what to do to diagnose this??

Many thanks for your help, Fiona

UPDATE

The table structure is:

CREATE TABLE [dbo].[TransactionLogging](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[InputMessage] [nvarchar](max) NULL,
[CCHMessage] [nvarchar](max) NULL,
[DocId] [char](20) NOT NULL,
[TransactionStatus] [char](5) NOT NULL,
[DateAdded] [datetime] NULL,
[LastUpdate] [datetime] NULL,
[SentDate] [datetime] NULL,

CONSTRAINT [PK_TransactionLogging] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY

Furthermore, the 2 most recent transaction timeouts occured for the first transaction of the day. This is no conincidence I'm sure!

Also just wondering if anybody has any thoughts on the following update to my code:

Any comments?!!!

 using (MiddlewareDBDataContext dc = new MiddlewareDBDataContext(ConfigurationWrapper.ActivityLoggingDatabase_ConnectionString))
            {

                dc.TransactionLoggings.InsertOnSubmit(new TransactionLogging()
                {
                    DateAdded = DateTime.UtcNow,
                    InputMessage = message,
                    DocId = documentID.ToString(),
                    TransactionStatus = transactionStatus
                });
                try
                {
                    dc.SubmitChanges();
                }
                catch (SqlException ex)
                {
                    //Wait for 30 seconds then retry..
                    System.Threading.Thread.Sleep(30000);
                    dc.SubmitChanges();
                }
            }

Upvotes: 2

Views: 1955

Answers (3)

Tony Hopkinson
Tony Hopkinson

Reputation: 20330

Unless you get lucky that's going to take some work to track down. given you are doing 1500 a day and you are getting one of these a week, I'd go with what Greg H suggested and deal with it when it happens.

Default connection timeout is 60 seconds, default command timeout is 15 seconds. If it was a connection failure I'd expect to see some other operations failing for the same reason around the same time. You could increase the command timeout, but that would be symptom fixing in my opinion, a pragmatic solution, but the nature of the issue suggests if you increase the load on the server, it's going to get worse, in which case thinngs could get silly.

I'd be looking at functions that do something heftty with the table, especially aggregate ones. Putting some sort of trace log around them might help you narrow it down. Definitley wonder if it's a worth while expenditure of effort though, given you have a 1 in 7500 failure rate in a five day operation. 1 in 10,000 on a 7 day...

Are there any triggers on this table by the way, if so they'll be worth looking at.

Upvotes: 0

Vitalii Ivanov
Vitalii Ivanov

Reputation: 3250

You should give more time for command to execute to prevent the timeout. This could be done in the following way:

 using(var db = new DataContext()) 
   { 
      db.CommandTimeout = 60; // seconds
      // your queries ...   
}

Upvotes: 2

Greg Hendershott
Greg Hendershott

Reputation: 16260

Well, SQL Server may take a variable amount of time to do an operation. (An INSERT in particular may require more work than you might expect.) The concept of a timeout is there for a reason.

So I think the answer is that timeouts are a reality that your code needs to deal with. You could increase the timeout value, so you get the exception less frequently. But also, you should probably catch the exception and do something reasonable, such as try to insert again and/or relay the error condition to the user.

Upvotes: 0

Related Questions