x

delete query hang in sql server 2005

we have a sql server 2005 database(Detailed version: Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)). When we run a delete query of the following kind, it goes hanging for almost 20hrs. Now, we can not make out why this should be so. also, how do we go about tracing the query execution within the sql db? Please advise in detail.

Hanging Query : delete from maportal_r where a_webc_url='JANUA/a-b-c/x.pdf'
more ▼

asked Jan 11 '12 at 01:58 AM in Default

Avijit gravatar image

Avijit
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first
  1. is it finished after 20 hours?
  2. did you check if there were any locks?
  3. it might also be caused by triggers on this table
If you run "select count(*) from maportal_r where a_webc_url='JANUA/a-b-c/ x.pdf'" you'll get the amount of rows being deleted. If this is also running for a long time, check your indexes (or the lack of it)
more ▼

answered Jan 11 '12 at 02:13 AM

Wilfred van Dijk gravatar image

Wilfred van Dijk
927 18 20 24

(comments are locked)
10|1200 characters needed characters left

To add to @Wilfred van dijk answer to be able to help here we need to know...

  • How big is the table maprotal_r (how many rows)?
  • How many rows are you trying to delete?
  • What is the structure of the table - can you give a DDL definition of the table
  • What indexes are on the table?
  • What recovery mode is the database in?
  • How full is the log file?
* Is there enough disk space for the log file to accommodate this transaction?
more ▼

answered Jan 11 '12 at 03:36 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

(comments are locked)
10|1200 characters needed characters left

First thing, run this query:

select * from sys.dm_exec_requests
See if you can see the statement that has been running for 20 hours. See if it shows a blocking processing. If not, you're just seeing a very big and long running delete statement. From there you need to do all the things that @Kev Riley is recommending because it sounds like you might have indexing issues, but I'd start with blocking.
more ▼

answered Jan 11 '12 at 04:02 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.6k 19 21 74

Jan 11 '12 at 04:15 AM Wilfred van Dijk
(comments are locked)
10|1200 characters needed characters left

In addition to Wilfred's answer, there could be many more reasons for this as it really depends upon your environment like

How big your table is? Either searching 100 rows without a proper index in a billion rows table could result in a table scan? Would need a huge memory for that. Moreover, Deleting million of rows could be resource intensive and with full recovery model and low log file growth rate it would become worse

Are there CONSTRAINTS OR Foreign keys with CASCADE DELETE OPTION? Missing indexes on referencing tables would hinder the performance (Same as above key point) and there would be locks to be held on those tables as well. This could trigger a chain of DELETES and LOCKS.

Are there too many indexes? Deleting high number of indexes values could be an issue as well

So try to delete the records in small and short batches to avoid blocking and keep your log file in check. For e.g

WHILE 1 = 1
BEGIN
DELETE TOP(2000)
FROM YOURTABLE
WHERE ;

IF @@ROWCOUNT < 2000 BREAK;
END
If you do not have an index on your a_webc_url column, try to add one if the table is huge. Since you know your environment and DB structure better, you are in a better position to sort it out.
more ▼

answered Jan 11 '12 at 04:16 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x51

asked: Jan 11 '12 at 01:58 AM

Seen: 1601 times

Last Updated: Jan 11 '12 at 06:42 AM