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, 2012 at 01:58 AM in Default

avatar image

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, 2012 at 02:13 AM

avatar image

Wilfred van Dijk
3.6k 29 39 49

(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, 2012 at 03:36 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

(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, 2012 at 04:02 AM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

Jan 11, 2012 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

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, 2012 at 04:16 AM

avatar image

Usman Butt
14.9k 6 13 21

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jan 11, 2012 at 01:58 AM

Seen: 2670 times

Last Updated: Jan 11, 2012 at 06:42 AM

Copyright 2018 Redgate Software. Privacy Policy