Lost data and no trace in the Transaction Log
-
Sometime between 10:15 pm on 08/24/05 and 4:35 pm 08/25/05 we lost 6 records (ea)
from 2 seperate tables
We can find no details in the Transaction Log, we used LogPI to read the log files,
the only way the records could have been deleted is from the SQL Server side, not through the
application( referential integrity hard coded in the app prevents single rows from being deleted)
that uses the tables.
Does anybody have any theories as to how items can be deleted and no records in the transaction logs?
Perpelexed in St. Louis
-
If you have the keys correctly configured, then SQL Server is no more capable of deleting the rows than your application is.
If you have T-Log backups configured, you can do a parallel restore (restore the db on the same server, but name it something else), and do a point in time recovery up to 10:15 or so.
If LOG PI didn't find them, they were either never there in the first place, or maybe they were added to a different table by accident. Either way, good luck!
-
I had a bunch of data (100,000s of records) disappear over the space of about 5 hours once...everything gone for that time period. Turned out that some dev hadn't got his transaction/error handling right. All the connections blocked (serialised access) up except this single one that had the transactions open for about 5 hours. So, everything looked like it was working, except for a lot of blocking. The support guy killed the blocking proc. It rolled back. Everything. As this transaction was never completed it doesn't seem to have gone to the log backups... all a bit exciting. Perhaps you had something similar?
-
I've had similar days
I use a cool monitoring tool called Spotlight that sends me an SMS message if there is any type of blocking lasting more than 2 minutes, just because of such situations.
Nicely done!
-
I have just had a similar experience. Sometime between 2pm and 3pm last Friday a table in an SQL Database lost 300+ records, with nothing in the transaction log to show this.
I did a point-in-time parallel restore to 2pm which shows all of the records present, and another to 3pm which shows the records missing. Examination of the transaction log backups for 2pm and 3pm (Using Apex SQL Log) show no indication that there were any deletions on this table.
The missing records were present in the DB for a period of weeks before they went away, so I don't think (?) that blocking is the issue here. The records would have been all entered at different times by different users, so I don't think a transaction roll-back could be the cause. Has anyone heard of this happening and have you heard of an explanation?
The one thing that is different here is that the SQL Server has been virtualized (VMWare) - could that be a factor? any known issues with SQL Server/VMWare? There are several other high-usage DB's on this server that have no problems whatsoever. This is a 3rd-party application/db that we have been experiencing some problems with data loss in this specific table. The vendor is at a loss to explain it except to say that "it must be VMWare" but I would be interested in any other opinions. I was unaware that records could go missing from a SQL Server table without leaving behind a trail.
Any thoughts would be appreciated.
Tim
-
The only sure-fire way I can recommend is to Evaluate a program called Log Explorer from Lumigent. If the data(all your data) is fairly critical, as well as the immediate retrieval of lost/archived daya, LOG PI is an excellent investment.
Basically you will point it at the specific T-Log that contains data from the time range you suspect it disappeared, then have it filter out everything but deletes on a specific table, you should then be able to determince who did what.
I don't think a VM makes a difference, either the tran is committed or it isn't
Good luck,
Pete
-
I have already looked at the transaction log using APEX SQL Log and filtered out everything but deletes. To my shock and surprise there were none. Could a tran stay open for weeks, then rollback?
Tim
-
yup, it is possible. You'd think somebody would have noticed it, but it is possible.
What isn't possible is that the data was readable during that period without the use of nolocks (dirty reads).
Other possibility, Recovery mode set to simpler for that period?
P
-
Yes, it is possible - the tran that caused me problems could easily have stayed open for weeks if we hadn't been monitoring the situation! As it was we caught it after about 4 or 5 hours - when we got into the office!
dbcc opentran is your friend...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply