delete query hang in sql server 2005

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)

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?

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.

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.