0

I have created one SQL job and it is executing a stored procedure to delete data from the table at a specific time.

But at the same time, if anyone will try to insert a record in that same table from another application through APIs then I am getting a timeout exception for that request. Error is

Microsoft.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Can I achieve both tasks at the same time without error?

Thanks in advance!

0

1 Answer 1

0

Probably you're locking the whole table as you're deleting more than 5k rows. In this condition sql server escalate the row lock to table lock.

You colud try to delete rows in groups of less than 5k in this way. Please be also sure to find rows using index seek (see the execution plan of the delete) and do not place inside a begin/commit transaction.

SELECT NULL --Makes the WHILE loop work.
WHILE @@ROWCOUNT <> 0
BEGIN
    DELETE TOP (4000) FROM YourTable
    WHERE Column < getdate()-31 -- or your filter
END

If this doesn't work you could look into table partitioning which would allow you to switch the data to another table very quickly, then truncate the data from the new table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.