Hi Experts,

Am witnessing a very strange issue when i try to insert some records . I get the message in the SSMS  like 5,10 etc rows affected. When i check for the same records in the table none of them is present.

This problem then automatically goes away after some time and all get backs to normal. I only have the access to that database no other user have the access. Totally confused about the all of a sudden new behavior of the database.

Kindly help to troubleshoot the Issue.

Thanks

Priya

so, how are you checking the data after the insert is done?

i assume you are committing the transaction after insert.

can you give us detailed step by step you were doing when you encounter the issue

Yes for sure.

I have a table XYZ . With columns Like ID,Name,Date,Address . Am not using rollback or commit block for inserts.

Simple Insert query embedded with a SELECT query for the insert.

Example INSERT INTO XYZ (ID,NAME,Date,Address) SELECT ID,NAME,Date,Address FROM ABC.

This might result in inserting 5 records 10 or even 300 plus depends upon the data available in the ABC.

Even if i try to insert manually some data into XYZ i get the message 5 rows affected but no data is present for the inserted values.

The table in use for INSERT XYZ already contains million plus records buts its an unusual behavior normally it allows inserts since past few days am regularly witnessing such issues due to that its affecting our daily load process. 

Thanks

ok. how are verifying the data you inserted?

are you using Select * from XYZ after the insert.

aslo, can you explain what happens when you query for the data after it was inserted? does it come backup with 0 rows or does the query run for longer time?

what is the isolation level -- do you snapshot isolation enabled?

i can think of scenerios, where if you do not commit the transaction, you would not see them, but you are saying, you are using any trasactions explicitly.

Hello Priya,

Is there may be an INSTEAD OF INSERT trigger on the table, which catches the insert and ignores it without any feedback?