Prev: Get minimum from table
Next: Outer Join showing default values instead of Null values
From: link285 on 31 Jan 2010 02:21
I have a sql server with many dbs. I don't get why when I delete a long running query(about 1 or 2 minutes) to complete, but while this is running, my other separate db on the same server hangs on some queries.

---
frmsrcurl: http://msgroups.net/microsoft.public.sqlserver.programming/
From: link285 on 31 Jan 2010 02:50
I should note that it is Sql server 2005 on windows server 2003.
Should a queries on one db locks another db?
I thought it should not even lock another table on the same db.

---
frmsrcurl: http://msgroups.net/microsoft.public.sqlserver.programming/Sql-Server-hang-when-deleting-many-records
From: Uri Dimant on 31 Jan 2010 03:38
Hi
How many rows are you deleting?
Try this approach (small batch of 2000 rows)

WHILE 1 = 1
BEGIN
DELETE TOP(2000)
FROM Foo
WHERE <predicate>;

IF @@ROWCOUNT < 2000 BREAK;
END


"link285" <user(a)msgroups.net/> wrote in message
news:eTzx8XkoKHA.1544(a)TK2MSFTNGP02.phx.gbl...
>I have a sql server with many dbs. I don't get why when I delete a long
>running query(about 1 or 2 minutes) to complete, but while this is running,
>my other separate db on the same server hangs on some queries.
>
> ---
> frmsrcurl: http://msgroups.net/microsoft.public.sqlserver.programming/


From: link285 on 31 Jan 2010 03:48
Hi,
I was deleting about 2k rows. But that's not really my concern. If I'm deleting from a table I don't want it to timeout a separate db's query. I can understand if it locks the same table but...

---
frmsrcurl: http://msgroups.net/microsoft.public.sqlserver.programming/Sql-Server-hang-when-deleting-many-records
From: Uri Dimant on 31 Jan 2010 03:54
Hi
A deletion consumes resources such as memory (lock operations) , cpu and
etc... So fo sure performing an efficient delete operation won't hurt
overall performance



"link285" <user(a)msgroups.net/> wrote in message
news:eQBqxIloKHA.3948(a)TK2MSFTNGP06.phx.gbl...
> Hi,
> I was deleting about 2k rows. But that's not really my concern. If I'm
> deleting from a table I don't want it to timeout a separate db's query. I
> can understand if it locks the same table but...
>
> ---
> frmsrcurl:
> http://msgroups.net/microsoft.public.sqlserver.programming/Sql-Server-hang-when-deleting-many-records


 |  Next  |  Last
Pages: 1 2
Prev: Get minimum from table
Next: Outer Join showing default values instead of Null values