SqlConnection timeouts not expiring

From: Preserved Moose (PreservedMoose_at_discussions.microsoft.com)
Date: 08/24/04 

Date: Tue, 24 Aug 2004 02:43:02 -0700

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