December 9, 2010 at 9:59 am
MS SQL Server 2008 SP1 Workgroup; Windows Server 2008 R2 Standard; RAID 5; 8 GB RAM, .MDB file size: 748MB, 8 loyal users
On 12/3 we discovered that all new records and all changes to existing records made between 11/11 and 11/18 were missing. The database stamps every record with a Create Date and a Modify Date. All records created during the 8-day period were missing. All records modified during the period rolled back to their 11/10 field entries.
We have ruled out user error and intentional user action for many reasons (application log of changes, no access to the server, no SQL log file evidence, extreme difficulty or impossibility of reversing changes to records 2 to 3 weeks old).
We have combed through the SQL Server logs and Windows Event log and see nothing unusual. Nothing was restored on the server. No use was made of the server other than to add new records and modify existing records after the latest good backup (12/2). The SQL backup of 12/2 at 11pm is perfect - no missing records or missing changes. So are all nightly SQL backups for previous nights.
We use the Simple model for backup. The 11pm nightly backup truncates the log file. No person has restored or rolled back any transactions, intentionally or otherwise.
It appears that somehow, 8 days' worth of writes to the database were rolled back. I don't know SQL Server well enough to understand how it is even possible on 12/3 to reverse changes made to all records modified from 11/11 to 11/18.
For example, here are values for the Create Date, Modify Date, and Description fields in a record. Users can change the Description but have no access to the Create Date and Modify Date fields.
As of 11/10 (Record was added by user)
Create Date: 11/10/2010
Modify Date: 11/10/2010
Description: Appointments
As of 11/11 (Record was modified by user)
Create Date: 11/10/2010
Modify Date: 11/11/2010
Description: Appointments 9 AM - 5 PM
As of 12/2 (No change since 11/11)
Create Date: 11/10/2010
Modify Date: 11/11/2010
Description: Appointments 9 AM - 5 PM
As of 12/3 (After data loss)
Create Date: 11/10/2010
Modify Date: 11/10/2010
Description: Appointments
Notice that the Modify Date field and Description field on 12/3 have rolled back to their 11/10 values. The customer experienced the same type of issue 4 months ago and is understandably very, very concerned.
What sort of event could cause this rollback? How can we prevent it from happening again? Thank you!
December 9, 2010 at 10:42 am
- someone may have restored your db to that point in time. (maybe targeted the wrong server.
- Do / did you have long running open transactions ???
- check your instances Errorlog file and default trace files for recovery actions.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 9, 2010 at 11:16 am
Thank you for the prompt response. Unfortunately, we have already covered these bases and found nothing to explain the rollback.
ALZDBA (12/9/2010)
- someone may have restored your db to that point in time. (maybe targeted the wrong server.
No one restored anything. It is a small office with a single SQL database. No one in the office has the rights or knowledge to perform a restore.
- Do / did you have long running open transactions ???
Certainly not intentionally! How would we check for running transactions?
- check your instances Errorlog file and default trace files for recovery actions.
The logs confirm no recovery / restore events.
Any help would be greatly appreciated!
December 10, 2010 at 1:51 am
Easiest way to show open transactions is using sql server management studio (SSMS).
Connect to your sqlinstance, double check you are connected to the correct database and then execute
dbcc opentran
another this that can be used is a dos command (on a client that has sqlclient installed or on the server itself):
sqlcmd -Syoursqlserver\theinstance -d yourdatabasename -Q"dbcc opentran" -o"c:\temp\opentran_result.txt"
The results of this cmd will be found in file c:\temp\opentran_result.txt
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 16, 2010 at 5:43 am
Thank you for the Open Transactions query. I have checked and there are no active open transactions. I will continue to check periodically to see if long-running open transactions appear at some point in the future.
Rolling back a group of old, open transactions might explain how the incident occurred. Unfortunately we still do not have a clue about why the incident occurred - what kind of event might have caused it. The logs are silent as far as I can tell.
Any ideas on why a group of transactions could spontaneously roll back without any intentional or accidental user action?
December 16, 2010 at 6:03 am
wa-sql (12/16/2010)
Any ideas on why a group of transactions could spontaneously roll back without any intentional or accidental user action?
They wouldn't. SQL doesn't randomly change data. The rule is that once a transaction is committed, it's changes are permanent (durability requirement of ACID)
If a transaction had been left open and somehow queries were reading through the held locks, then the connection closed without a commit, SQL would roll back the changes
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply