SQL Server 2008 - SQL timeout exception on Insert Linq query

Question

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();
                }
            }

Answer 1

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 ...   
} 

Answer 2

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.

Answer 3

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.