Missing rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Missing rows

Author

Topic

petrhanus
Starting Member

2 Posts

Posted - 2012-01-09 : 12:21:25

Hi, I have this problem - there occuring missing records, identity sequence is not continuous. We have found it and attached two triggers one for delete, one for update, both recording deleted rows into another table (kind of wastebasket). But there are still missing records and no one can understand how it is possible.

We can exclude situation that triggers are disabled, or that delete is realized over view.

Thanks for any idea how to catch the problem roots ???

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-09 : 12:26:40

IDENTITY_INSERT ON?

Are the gaps happening now that the FOR DELETE Trigger is now on and you can't see the deleted rows...or are you trying to figure out past DML Operations? If the latter...good luck

Do you have a backup?

Can you replicate the problem or are you trying to figure out why this is happening?

If the trigger is enabled, you will see all the deletes...well perhaps...post the code for the trigger...there may be a problem with that as well

We don't know unless you post concrete details

AND yet ANOTHER Reason to ONLY allow Sproc access to your data

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

petrhanus
Starting Member

2 Posts

Posted - 2012-01-10 : 04:14:58

This is myysterious situation - these records were in the database for years, then suddenly disappeared without trace.
Table is about 1/2 million records, about dozen is missing, but how on the God's green earth this can occure ???

Sachin.Nand

2937 Posts

Posted - 2012-01-10 : 04:22:48

Was there any corruption occurred and someone ran DBCC CheckDB?

After Monday and Tuesday even the calendar says W T F ....

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-10 : 12:19:35

SELECT * INTO newTable WHERE Cond = What I want to keep

bcp 'SELECT * FROM old Table WHERE Cond = what I want to lose' queryout 'D:\Test.dat' ....

DROP TABLE oldTable

sp_rename newTabel oldTable

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources