asked on

SQL Server Timeout issue when updating one row

Hi,

I need help fixing the following issue:

In some cases users of my websiteI get the following error message "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." when updating their own data. (Meaning: no mass update, but only updating a row at a time).

The issue only occurs on some of the records but if it has started happening on a record it will always timeout from then on. Just like it would remain locked forever or as if the data was broken somehow.

The StackTrace looks like this:
-----------------------------------
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at MyOwnNamespace.WLDB.UpdateWLText(Int32 WLID, String Text1, String Text2)
at ASP.EditWL_aspx.btnSafe_Click(Object sender, EventArgs e)
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain()

Any ideas? Please help.

Guy Hengel [angelIII / a3]

can you post the update statement in question, the table and index structure relate to this update?

samahe

ASKER

Hi,

here is the update statement:
------------------------------------
public void UpdateWLText (int WLID, String Text1, String Text2) {

// Create Instance of Connection and Command Object
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
SqlCommand myCommand = new SqlCommand("SPROC_xxx", myConnection);

// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure;

// Add Parameters to SPROC

SqlParameter parameterWLID = new SqlParameter("@ItemID", SqlDbType.Int, 4);
parameterWLID.Value = WLID;
myCommand.Parameters.Add(parameterWLID);

SqlParameter parameterText1 = new SqlParameter("@Text1", SqlDbType.NVarChar, 800);
parameterText1.Value = Text1;
myCommand.Parameters.Add(parameterText1);

SqlParameter parameterText2 = new SqlParameter("@Text2", SqlDbType.NVarChar, 200);
parameterText2.Value = Text2;
myCommand.Parameters.Add(parameterText2);

// Open the database connection and execute SQL Command
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
}

and this is the SPROC:
--------------------------

CREATE PROCEDURE SPROC_xxx

(
@ItemID int,
@Text1 nvarchar(800),
@Text2 nvarchar(200)
)
AS

UPDATE Wedding_WishListHdr

SET

Text1 = @Text1,
Text2 = @Text2

WHERE
ItemID = @ItemID
GO

ItemID is primary key.

Guy Hengel [angelIII / a3]

do you have any triggers on that table?

Ved Prakash Agrawal

Hi,

There are few things which can help you to find out the solution.
1. try to Set the Timeout for executing the procedure around five minute.
2. use sp_who2 and see into the database server , there is any block with repective table or not. and also tried to find which process Id is blocking your process.
3. check the code of that process which is execute by someone.
or you can Kill that process which process block your process .
4. and then your process are executed successfully or not.

plz check and revert.
if again you are facing same problem please revert.

ved

samahe

ASKER

Hi,

Re. the trigger: there is none that I know of.

I tried sp_who2 but I'm not sure how to interprete the results:

SPID Status Login
HostName BlkBy DBName Command CPUTime DiskIO LastBatch
ProgramName SPID
----- ------------------------------ ----------------------------------
---------- ----- ------ ---------------- ------- ------ --------------
---------------------------- -----

1 BACKGROUND sa
. . NULL LAZY WRITER 10 0 02/14 11:11:43
1
2 sleeping sa
. . NULL LOG WRITER 0 0 02/14 11:11:43
2
3 BACKGROUND sa
. . master SIGNAL HANDLER 0 0 02/14 11:11:43
3
4 BACKGROUND sa
. . NULL LOCK MONITOR 0 0 02/14 11:11:43
4
5 BACKGROUND sa
. . master TASK MANAGER 0 2 02/14 11:11:43
5
6 BACKGROUND sa
. . master TASK MANAGER 0 0 02/14 11:11:43
6
7 sleeping sa
. . NULL CHECKPOINT SLEEP 0 1 02/14 11:11:43
7
8 BACKGROUND sa
. . master TASK MANAGER 0 1 02/14 11:11:43
8
9 BACKGROUND sa
. . master TASK MANAGER 0 0 02/14 11:11:43
9
10 BACKGROUND sa
. . master TASK MANAGER 0 0 02/14 11:11:43
10
11 BACKGROUND sa
. . master TASK MANAGER 0 0 02/14 11:11:43
11
12 BACKGROUND sa
. . master TASK MANAGER 0 0 02/14 11:11:43
12
13 BACKGROUND sa
. . master TASK MANAGER 0 0 02/14 11:11:43
13
51 sleeping HOSTNAME\ASPNET
HOSTNAME . Portal AWAITING COMMAND 0 0 02/14 16:46:53
.Net SqlClient Data Provider 51
52 sleeping MYHOST\ASPNET
MYHOST . Portal AWAITING COMMAND 0 0 02/14 16:46:55
.Net SqlClient Data Provider 52
53 sleeping MYHOST\ASPNET
MYHOST . Portal AWAITING COMMAND 0 0 02/14 16:47:00
.Net SqlClient Data Provider 53
54 sleeping MYHOST\ASPNET
MYHOST . Portal AWAITING COMMAND 0 1 02/14 16:47:03
.Net SqlClient Data Provider 54
55 sleeping MYHOST\ASPNET
MYHOST . Portal AWAITING COMMAND 0 0 02/14 16:48:02
.Net SqlClient Data Provider 55
56 sleeping MYHOST\ASPNET
MYHOST . Portal AWAITING COMMAND 0 0 02/14 16:48:29
.Net SqlClient Data Provider 56
57 sleeping MYHOST\ASPNET
MYHOST . Portal AWAITING COMMAND 0 0 02/14 16:53:02
.Net SqlClient Data Provider 57
58 RUNNABLE MYHOST\Admin
MYHOST . Portal SELECT INTO 60 17 02/14 16:53:45
OSQL-32 58
59 sleeping MYHOST\ASPNET
MYHOST . Portal AWAITING COMMAND 0 0 02/14 16:53:14
.Net SqlClient Data Provider 59
60 sleeping MYHOST\ASPNET
MYHOST . Portal AWAITING COMMAND 0 0 02/14 16:53:14
.Net SqlClient Data Provider 60
61 RUNNABLE MYHOST\ASPNET
MYHOST . Portal SELECT 0 0 02/14 16:53:14
.Net SqlClient Data Provider 61
62 sleeping MYHOST\ASPNET
MYHOST . Portal AWAITING COMMAND 0 0 02/14 16:53:14
.Net SqlClient Data Provider 62
63 sleeping MYHOST\ASPNET
MYHOST . Portal AWAITING COMMAND 10 0 02/14 16:53:16
.Net SqlClient Data Provider 63
64 sleeping MYHOST\ASPNET
MYHOST 61 Portal UPDATE 0 0 02/14 16:53:23
.Net SqlClient Data Provider 64

(27 rows affected)
1>

I dont' t dare to set the timeout to 5 minutes as there must be an underlying issue with a timeout on a single row update.

samahe

ASKER

One more thought re. trigger or maybe a lock:
I had updated a few records using WebMatrix or Visual Studio. Do you think there may have been a conflict?
If so: do you have an idea how to find out and fix it?

Guy Hengel [angelIII / a3]

>I had updated a few records using WebMatrix or Visual Studio. Do you think there may have been a conflict?
possibly, if that session did not commit the changes.

samahe

ASKER

Can you tell me, how I can find out if the session did not commit the changes? Is there a way to fix the impacted rows?

teeg01

Is the SQL Server performance poor for all updates (different tables and stored procedures) or just for this particular Update statement?

samahe

ASKER

Both the server and the Update statement are usually quite fast.
The timeout problem only occurs for those rows that seem to be somehow inaccessible.

samahe

ASKER

Hi,

Do I need to provide further information so someone can help me?
The issue keeps happening. So I really do need help.

Guy Hengel [angelIII / a3]

can you look at the lock information when the update times out?
with sp_lock resp. the Enterprise Manager Activity locks per object you can get details about such lockings.

samahe

ASKER

Hi,

this is the result of sp_lock while the update times out.

spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- ---------------- -------- ------
51 5 0 0 DB S GRANT
52 5 0 0 DB S GRANT
53 5 0 0 DB S GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
56 1 85575343 0 TAB IS GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
59 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
61 5 0 0 DB S GRANT
62 5 0 0 DB S GRANT
63 5 0 0 DB S GRANT
64 5 0 0 DB S GRANT
65 5 0 0 DB S GRANT
66 5 0 0 DB S GRANT
67 5 0 0 DB S GRANT
67 5 1042102753 2 KEY (da001e795297) U GRANT
67 5 1042102753 0 RID 1:151:3 X CNVT
67 5 1042102753 2 PAG 1:153 IU GRANT
67 5 1042102753 0 PAG 1:151 IX GRANT
67 5 1042102753 0 RID 1:151:3 U GRANT
67 5 1042102753 0 TAB IX GRANT
68 5 0 0 DB S GRANT
69 5 0 0 DB S GRANT
70 5 0 0 DB S GRANT
71 5 0 0 DB S GRANT
72 5 0 0 DB S GRANT
72 5 1042102753 0 RID 1:151:3 S GRANT
72 5 1042102753 0 PAG 1:151 IS GRANT
72 5 1506104406 0 TAB IS GRANT
72 5 1042102753 2 PAG 1:153 IS GRANT
72 5 1010102639 0 PAG 1:161 IS GRANT
72 5 1010102639 0 TAB IS GRANT
72 5 1042102753 2 KEY (da001e795297) S GRANT
72 5 1506104406 0 PAG 1:233 IS GRANT
72 5 1042102753 0 TAB IS GRANT

1>

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

samahe

ASKER

You are right. the object id is 1042102753.
I need to check a few things based on what I found with the DBCC INPUTBUFFER (72)
but I will let you know when I'm done.

samahe

ASKER

THANK YOU! I now found the cause of the problem (actually me, doing somthing really stupid in the stored procedure that was behing DBCC INPUTBUFFER (72)) This SPROC took really long because an index was missing and so the table was locked in some cases when the Update was trying to access the row.