I have a SQL Server 2000 SP2 database. I can work on it all day, and everything is fine. Backups run at 7:00pm successfully. At some point after that, usually about midnight, all changes are rolled back. I can restore from the 7:00pm backup, but overnight every change (including those restored) will again disappear.
I have tinkered for two weeks, checking every setting and running every automated job (there are three) interactively. Nothing seems to cause or cure this problem. But every morning when I come in, my database is back to where it was on or about March 15.
One possible clue: there is a SQL log entry stating "Transaction Log Truncated" that appears to happen about the same time - midnight. I do not know why this is happening or whether it is significatnt; I truncated the transaction log interactively, with no negative effect. Also, the problems began after I installed SP2.
Any ideas?Probably best to leave the profiler running - logging t a text file to find out whats happenning.
It sounds like something is restoring a backup.
If your backup restore does hold the data then it gets lost again at midnight then there must be a task that is deleting everything that has been added after march 15 or a backup is being restored from that date.
Try creating a new table - if that disappears then you will now that it isn't just a task that deleted data.
Check with the network people that they aren't trying to backup the disk. This will probably lock the database files and crash the server. If you are holding open transactions then all changes in those transactions would be lost.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment