RE: SqlConnection timeouts not expiring

From: Eric (Eric_at_discussions.microsoft.com)
Date: 08/25/04 

Date: Wed, 25 Aug 2004 12:21:02 -0700

Have you tried String strCommand = "Truncate Table " + m_strTable;
Removes all rows from a table without logging the individual row deletes.
Much faster
- Eric
"Preserved Moose" wrote:

> Hi,
>
> I have a simple application that launches a worker thread on a timed
> interval (usually an hour). The thread creates a SqlConnection object and
> opens it thus
>
> try
> {
> m_sqlConnection = new SqlConnection (m_strConnection);
> m_sqlConnection.Open ();
> ...
>
> I then call a stored proc
>
> ...
>
> SqlCommand sqlCommand = new SqlCommand (strStoredProcedure,
> m_sqlConnection);
> sqlCommand.Parameters.Add ("@dateFilter", SqlDbType.DateTime).Value =
> DateTime.Today;
> sqlCommand.CommandType = CommandType.StoredProcedure;
> sqlCommand.CommandTimeout = 600; // 10 minutes
>
> Int32 iRowsLoaded = sqlCommand.ExecuteNonQuery ();
>
> ...
>
> which executes successfully. I then perform various inserts into tables in
> the normal form (code is hidden inside objects with the connection passed in)
>
> ...
>
> m_sqlCommandInsert = new SqlCommand ("INSERT INTO " + m_strTable +" (Sku,
> Lot, Storekey, Id, Qty, Adddate, Loc, PartNo, Owner, Location, Brand, Type,
> Loaded, FileCreated)" + " VALUES (@v0, @v1, @v2, @v3, @v4, @v5, @v6, @v7,
> @v8, @v9, @v10, @v11, @v12, @v13)");
>
> m_sqlCommandInsert.Connection = sqlConnection;
>
> // add in variables
>
> m_sqlCommandInsert.ExecuteNonQuery ();
>
> ...
>
> which is fine. I then try and run a command to clear a very large table
> which can take up to 1-2 mins
>
> ...
>
> String strCommand = "DELETE FROM " + m_strTable;
>
> SqlCommand sqlCommand = new SqlCommand (strCommand, sqlConnection);
> sqlCommand.CommandTimeout = 600; // 10 minutes
> sqlCommand.ExecuteNonQuery ();
>
> ...
> however, for some reason it randomly locks here.
>
> My expectation would be that it would either be successful and return, fail
> and throw an SqlException or time-out and throw an SqlException.
> In fact it just locks - the main application is fine, it's just the thread
> waiting on this coming back.
> If I then, as I did this morning, open up Query Analyser and do a count(*)
> on the table, it seems to release it and the program continues - this is
> after sitting dead for 3 hours.
>
> The problem is that I can't even email someone (me) to indicate a problem as
> the app has locked.
>
> I am running this on SqlServer 2000 on Windows Server 2000 SP4 using the
> .NET framework version 1.1 (version 1.1.4322.573).
>
> Any thoughts would be greatly appreciated.
>
> Regards
>