The housekeeping statement is
delete from event where eventtime >= '2005-07-01' and eventtime <
'2005-07-02'
There is around 4x,xxx records per day. It takes around 5 minutes to
remove.

The blocked statement is
insert into event values ( ......, '2005-10-06')
When delete is in progress. starting this statement will result as
blocking until the delete finishs.

I can simulate the problem in Query Analyser. I am trying to set
isolation level and table hint. But it is still not working. I want to
know if SQL Server 2000 can handle both statements simultaneously?

Thanks.

-Morris

Morris
In this situation you can set lock time out value .This should help you
BOL Has this:
Customizing the Lock Time-out
When Microsoft® SQL Server™ 2000 cannot grant a lock to a transaction on a
resource because another transaction already owns a conflicting lock on that
resource, the first transaction becomes blocked waiting on that resource. If
this causes a deadlock, SQL Server terminates one of the participating
transactions (with no time-out involved). If there is no deadlock, the
transaction requesting the lock is blocked until the other transaction
releases the lock. By default, there is no mandatory time-out period, and no
way to test if a resource is locked before locking it, except to attempt to
access the data (and potentially get blocked indefinitely).



Note The sp_who system stored procedure can be used to determine if a
process is being blocked, and who is blocking it.


The LOCK_TIMEOUT setting allows an application to set a maximum time that a
statement waits on a blocked resource. When a statement has waited longer
than the LOCK_TIMEOUT setting, the blocked statement is canceled
automatically, and error message 1222 "Lock request time-out period exceeded"
is returned to the application.

However, any transaction containing the statement is not rolled back or
canceled by SQL Server. Therefore, the application must have an error handler
that can trap error message 1222. If an application does not trap the error,
it can proceed unaware that an individual statement within a transaction has
been canceled, and errors can occur because statements later in the
transaction may depend on the statement that was never executed.

Implementing an error handler that traps error message 1222 allows an
application to handle the time-out situation and take remedial action for
example, automatically resubmitting the statement that was blocked, or
rolling back the entire transaction.

To determine the current LOCK_TIMEOUT setting, execute the @@LOCK_TIMEOUT
function, for example:

DECLARE @Timeout int
SELECT @Timeout = @@lock_timeout
SELECT @Timeout
GO


See Also

@@LOCK_TIMEOUT

SET LOCK_TIMEOUT

sp_who



--
Regards
R.D
--Knowledge gets doubled when shared


[quoted text, click to view]