Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How was the record deleted!! Expand / Collapse
Author
Message
Posted Tuesday, August 14, 2012 3:01 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 7, 2014 4:24 PM
Points: 70, Visits: 195
Hi All
I am having a strange issue.
From .NET application 5 records are inserted in a Table A.
These records are inserted at datetime=2012-08-14 14:14:49.000

There is another functionality where another user , from the .net application, approves these records.
The code here is not touching table A at all.
It is only inserting a record in another table TableAudit.
In TableAudit the record is inserted at 2012-08-14 14:28:20.000

When the user displays the records what he inserted in Table A, 1 record is missing.
From SQL server, I check the Table A and it seems as if that record was never inserted. Its not there.
But according to the validations in the application this record must have been inserted ( i am sure because in the table the ids exist from 78869 to 78874 but 78871 is missing) , so I go and see the delete log.
It shows a record was deleted from this table at the same time when the record was being inserted in TableAudit

Such logic is no-where in the application to delete 1 record from Table A while inserting record in TableAudit.

Any idea how this can happen in SQL Server? How can I find more information on this deleted record.
Thanks!


Post #1345042
Posted Tuesday, August 14, 2012 3:08 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:16 PM
Points: 990, Visits: 2,223

Are there any triggers on those tables?

Post #1345044
Posted Tuesday, August 14, 2012 3:30 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 7, 2014 4:24 PM
Points: 70, Visits: 195
rVadim (8/14/2012)

Are there any triggers on those tables?



No triggers.
Post #1345056
Posted Tuesday, August 14, 2012 3:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 12,905, Visits: 32,165
if a transaction rolls back or fails, the identity() columns still increment.
it sounds like an error occurred inserting the data that was not handled/reported by the .net application.

i've seen things form an app, like sticking zero in a foreign key column, or leaving values null for NOT NULL columns, for example.
those things return an error from SQL, but the app might jsut be assuming that the data was updated/inserted, without displaying any errors returned.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1345060
Posted Tuesday, August 14, 2012 3:41 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:16 PM
Points: 990, Visits: 2,223

78869 to 78874 - that is 6 records you said you inserted 5 ?

Post #1345062
Posted Tuesday, August 14, 2012 3:47 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 7, 2014 4:24 PM
Points: 70, Visits: 195
rVadim (8/14/2012)

78869 to 78874 - that is 6 records you said you inserted 5 ?



I also add that 78871 was missing.
78869
78870
78872
78873
78874

Sorry for the confusion
Post #1345063
Posted Tuesday, August 14, 2012 3:53 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 7, 2014 4:24 PM
Points: 70, Visits: 195
Lowell (8/14/2012)
if a transaction rolls back or fails, the identity() columns still increment.
it sounds like an error occurred inserting the data that was not handled/reported by the .net application.

i've seen things form an app, like sticking zero in a foreign key column, or leaving values null for NOT NULL columns, for example.
those things return an error from SQL, but the app might jsut be assuming that the data was updated/inserted, without displaying any errors returned.


I used this to see if any deletions happened on that table , and it gave me 1 [Transaction ID]
So its like the record was inserted and then deleted?? isn't it?

DECLARE @TableName sysname
SET @TableName = 'dbo.Table A'
SELECT
[Transaction ID]
FROM
fn_dblog(NULL, NULL)
WHERE
AllocUnitName LIKE @TableName + '%'
AND
Operation = 'LOP_DELETE_ROWS'
Post #1345066
Posted Wednesday, August 15, 2012 3:24 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 28, 2014 1:59 PM
Points: 179, Visits: 827
You could always run a server side trace looking for the events and especially the TM: Rollback Tran Starting/Completed event to spot if the transaction was rolled back

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
Post #1345181
Posted Wednesday, August 15, 2012 3:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 6,811, Visits: 14,025
SJanki (8/14/2012)
Hi All
I am having a strange issue.
From .NET application 5 records are inserted in a Table A.
These records are inserted at datetime=2012-08-14 14:14:49.000

There is another functionality where another user , from the .net application, approves these records.
The code here is not touching table A at all.
It is only inserting a record in another table TableAudit.
In TableAudit the record is inserted at 2012-08-14 14:28:20.000

When the user displays the records what he inserted in Table A, 1 record is missing.
From SQL server, I check the Table A and it seems as if that record was never inserted. Its not there.
But according to the validations in the application this record must have been inserted ( i am sure because in the table the ids exist from 78869 to 78874 but 78871 is missing) , so I go and see the delete log.
It shows a record was deleted from this table at the same time when the record was being inserted in TableAudit

Such logic is no-where in the application to delete 1 record from Table A while inserting record in TableAudit.

Any idea how this can happen in SQL Server? How can I find more information on this deleted record.
Thanks!


You've stated that 5 rows are inserted from the app - and 5 rows remain.
If you're inserting 5 rows one at a time, what prevents another process from inserting then deleting one row in the middle of those inserts? Where did the sixth row come from?
Why aren't you inserting the 5 rows in one statement?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1345184
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse