strange data behavior used and losted
-
Greetings to all:
I have the MOST weird issue i've ever seen.
the thing is that we have a Database, in wich to get the full data several parts of tha application saves data in different tables for instance:
1. saves data to the master table (tbl_vehicles), generates an unique number "serial" (varchar 13 ) to this record. This is saved to another table called "progress" to audit the data.
2. saves data to some of the detail tables (tbl_owners) vinculated by the unique number "serial". This is saved to another table called "progress" to audit the data.
3. saves data to the rest of the detail tables (tbl_debts) vinculated by the unique number "serial". This is saved to another table called "progress" to audit the data.
This happens in about 20 minutes after that the user prints out a report on the vehicle, extracting data from the mentioned tables. say this all happened between 14:00 and 14:20, among other transactions. The server hung up (aparently for low performance reasons)
At 14:30 the server is reloades and the tables that holded this data... well simply doesn't hold it anymore, it's just not there and another record took the "serial" unique identifier number assigned.!!!!
I don't get how we may have printed the information accesing to the data tables and some time later the data is not there.
There is no trace about it even at the progress table. (our auditting method is based on triggers)
There is a small period of time that doesn't register any activity (acording to the progress (audit) table) between 14:24 and 14:31, wich was aproximately the time the server hunged up and had to be rebooted.
Unfortunately i learned about this behavior the next day, and the person in charge of the system, got the daily "complete backup" truncating the log. So i can't have access to the log to review the data.
Well, my question is, is it possible to SQL Server to delete data in this way? what may cause this kind of problems? what can i do?
Best regards to all.
Jorge
-
Well i'm thinking right now... how come no one answered this one?
maybe it was too simple or too complicated or i just didn't posted it right. Any way. i've been reading a lot and i think i 've found an answer. (i hope some answers this one
)Cheking the SQL server 2000 's input / output Architecture, i ran into this:
"SQL server 2000 allocates much of its virtual memory to a buffer cache and uses the cache to reduce physical I/O.....
The data remains in the cache until it has not been referenced for sometime....
DATA CAN BE CHANGED MULTIPLE TIMES BY LOGICAL WRITES BEFORE A PHYSICALL WRITE TRANSFERS THE NEW DATA BACK TO THE DISK."
So now i'm thinking. Is it possible that the data that was saved actually was on the buffer cache, so it never make to the physical disk?.
i believe so, besides it's the only rational explanation i've found.
Wich makes me wonder again, what may i do to avoid this from happening again?
i 'm running three possibilities:
1. Redisign the transaction model of the application. Right now it is almost transactionless.
2. Finding out the way to ensure the physical write of data.
3. Write a Log from the client application, ensuring to record to a flat file every SQL transaction (the ones that write the data). This way if something like this would ever happen again i would be able to reconstruct the information.
Well i hope someone finds this interesting.
Regards to all
Jorge
-
Jorge,
I can think of at least two possiblities:
1) The updating of information was in the middle of a transaction and when the server went down and was brought back up, it rolled back any incomplete transactions.
2) A System person did a restore of the database and there were not transaction log backups for the time frame you were looking at.
Steve
-
Not a lot of information was given about your, 'hung', server. Was there a rollback to a transaction underway? How was the issue resolved? Cut the power to the server or forced a reboot to occur?
Since SQLServer enforces the ACID principles within its architecture, for you to 'lose' data, something outside the control of SQLServer would have had to occur to wipe out data that had not been written to disk.
Once the server was rebooted it would have gone into recovery mode. Since the data you were looking for is missing, it obviously never got written to disk.
To fix this?
Control the length of the transactions. If you don't specify a transaction, SQL Server provides an implicit transaction that runs the length of your batch. You might want to separate each table operation with it's own transaction.
Shorten the time interval between trans log backups. Make sure that you are doing transaction log backups. If they are running every 30 minutes, you might want to shorten the interval to 15 minutes or less.
Now the timeline may not have been completely accurate, but your post indicated that the insert of one invoiceID across the database took 20 minutes. This would seem a bit excessive. I would think that one could insert a few hundred MB of data in 20 minutes. I would recommend taking a look at the execution plans of these inserts. Your table indexes may be inhibiting your ability to insert into these tables.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply