Issue when deleting a record from a large table

can you put your table structure along with all the indexes and the delete statement /

Is it possible the server is running out of transaction log space at that point?

Are you really updating the row? Could you not update the changed content versus deleting? If this is SQL 2008, you may want to look into the MERGE command and see if it helps. As far as why this is happening, both the DELETE and the INSERT will have to write to the transaction log in addition to any indexing overhead of either transaction. Therefore, with a large number of rows, you could be getting performance issues because of either if you are truly using a loop, which will process row-by-row. If you can somehow batch both processes, it may help performance. Again, the MERGE statement in SQL 2008 can handle UPDATE, INSERT, or DELETE in one statement.

You can accomplish something similar with SQL 2005 or higher with the OUTPUT clause. You can perform the INSERT or the DELETE transaction first, capturing the necessary key information (whatever associates the records). Using the information from OUTPUT, you can run a subsequent transaction to perform the other action.

Sync table

TableName      varchar(MAX)      Unchecked
Id      int      Unchecked
Type      int      Unchecked
SyncID      int      Unchecked << Clustered Index


          ALTER PROCEDURE [dbo].[Portal_Sync_Delete] @pID int AS BEGIN DELETE From Sync WHERE SyncID = @pID End

use this sp and try

ALTER PROCEDURE [dbo].[Portal_Sync_Delete]
@pID int
WITH RECOMPILE
AS
BEGIN
  DELETE From Sync WHERE SyncID = @pID
End

if it is related to parameter sniffing, then it should fix

Still the same unfortunately, although this time it did 100 more records

I expected as much as I think the issue is in the entire process of INSERT and calling the stored proc to DELETE in a loop versus batch is the problem. Therefore, I would take a look at  what you are doing in total. Even if you require a loop on the INSERT, maybe store the pID values in a temp table or table variable with the pID indexed, then run the DELETE via a JOIN.

Ah I see the confusion, there is no insert into the table simply a delete.

How big is the table ? any triggers on it ?

Okay, then why not just DELETE in batch?
The above questions on table size are good to answer as well as confirmation that you checked the disk space and size of transaction log. Again, you may be running into an issue where you are filling up the transaction log with the DELETEs, so you are unable to process more rows.

there are no triggers, the code is running fine in numerous installations, the table acts like a stack it is every growing / reducing

Any other ideas?

Perhaps if you answered some of the questions it would help.  

For example:

can you put your table structure along with all the indexes and the delete statement /

 This is where you get a chance to post the CREATE TABLE including all the CREATE INDEX structures.

Is it possible the server is running out of transaction log space at that point?

And knowing this would help to see if the data file is increasing at that point, hence the delay.  Of course if you have Auto-shrink on all bets are off.

Okay, then why not just DELETE in batch?

Still no answer other than the code is running fine in numerous installations which is irrelevant as you are not concerned with the other installations, are you?

So all in all, if you still need help, take a step back and review all the questions and see where you can help us help you.  If there is a question you do not understand, then ask.  For example, perhaps the reference to the Transaction Log and the role it plays is alien to you, then now would be a good time to ask.  That is if you still need help.

Resolved ourselves
Excellent!  Now please close the question.

Answer in comment