mystery of deleted rows
-
Thanks in advance for your feedback! Here's the case.
Users notice missing data in their application when not using the delete button (stored procedure). I created a trigger to capture this missing data and it revealed through the transaction log that SQL Server was cleaning up deleted rows from the indexes/pages. The developer has to go back in and put these rows back in the table but has noticed every time these deletes are reinserted, SQL Server deletes another batch with the same ones from the previous time. So this started at 85 rows and over the week has grown to 3500 rows of deletes by appending undos and adding new ones. The stored procedure has been used for over a year with no problems and works daily when users need to edit content.
I can see in the transaction log the deleted rows since they are marked as ghost and done in one transaction. I have used profile to create a trace but have missed these occurrences.
What more can I do to help pin point the cause of deletes that the developer says should not be deleted at all? Meaning, the users are not initiating the delete function in the application.
-
Can you post your DELETE code?
I wasn't born stupid - I had to study.
-
CREATE PROCEDURE del_showcode
@NumId int,
@TagId int,
@PartDel bit OUTPUT
AS
DELETE tableNameA WHERE (colNameC IS NULL) AND (TagID = @TagId)
DECLARE @Count Int
SELECT @Count = count(*) FROM tableNameA WHERE NumId = @NumID
IF @Count = 0
BEGIN
DELETE tableNameB WHERE (NumId = @NumId)
SET @PartDel = 1
END
ELSE
SET @PartDel = 0
RETURN @PartDel
Below is a count on how many deleted rows occurred and what time. The blue highlighted rows indicate normal deletes and the red highlighted rows indicate SQL Server doing the batch of deletes. As you can see, yesterday we had two batch deletes of 3000+ rows.May 19 2005 12:36PM 1
May 19 2005 12:33PM 1
May 19 2005 11:49AM 1
May 19 2005 10:49AM 7
May 19 2005 10:03AM 2
May 19 2005 8:24AM 2
May 19 2005 8:02AM 12
May 19 2005 7:53AM 1
May 18 2005 11:46AM 3312
May 18 2005 11:20AM 1
May 18 2005 10:58AM 1
May 18 2005 10:11AM 2
May 18 2005 8:55AM 12
May 18 2005 5:41PM 12
May 18 2005 5:40PM 6
May 18 2005 5:22PM 6
May 18 2005 5:20PM 6
May 18 2005 4:43PM 1
May 18 2005 4:33PM 7
May 18 2005 4:00PM 3
May 18 2005 3:53PM 1
May 18 2005 3:18PM 3349
May 18 2005 3:05PM
-
I'd be looking at anything and everything that has been changed in the application at the time that this started. Its got to be something in the application doing this. I would also recommend using profiler to identify where the deletes are coming from. This seems serious enough to not worry about the overhead that profiler causes. Just try to narrow your trace enough that you don't fill your disk with the trace info, and let it run until the next occurence. Identify the users that are in the system at the time the delete occurs, and ask them what EXACTLY they did. Then isolate them, and have them do it again. You should be able to identify what user caused it, and what parts of the application they were executing at the time. Then you can look for misplaced/malformed delete statements.
Steve
-
could it be that when the procedure is called, @NumId is null, and produces unpredicatable results, like deleting everything?just a thought.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! - I never captured the actual deleted rows with the profile trace, I only could see the batch in the log files which led me to a user group to a different programmer. Which pointed to a delete query that started the ball rolling. It was brought to my attention that with this statement:DELETE * FROM TABLEA WHERE NumId = ' 'caused another update sproc to cast it as a zero. And I'm told that since this parameter was not set to null that it caused the delete sproc posted here to fire off. The solution was to set this parameter in the update sproc to null. I'm not able to go into anymore details here since the code was not fully provided but the change did solve the delete problem.Programmers in my environment have freedom to write sql queries as they wish.Thanks again for everyone's time.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply