timeouts!!

[psawant]

hi!

I am having some timeout issues.

I am running sql 2k with 3gig available ram.

I did a 600,000 record delete on a table that gets written to by the
active/production application and my application timed out when it was
doing the delete.

why did stored procedures that ran fine before I started the large
delete slow down?
Some of the procedures which slowed down were accessing the same table
where i was doing the delete.

Thanking you in advance!!

parez

[Erland Sommarskog]

(ps*****@gmail.com) writes:

I am having some timeout issues.

I am running sql 2k with 3gig available ram.

I did a 600,000 record delete on a table that gets written to by the
active/production application and my application timed out when it was
doing the delete.

why did stored procedures that ran fine before I started the large
delete slow down?
Some of the procedures which slowed down were accessing the same table
where i was doing the delete.

If you delete 600000 rows in one table, there is a fair chance that
SQL Server will take out a table lock on that table, which means that
no other processes can access it as long as the delete is running.

In any case, such a massive delete in a busy production system needs to
be run during off-hours or a maintenance window. Running in the middle
of the day is not a very good idea.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp