April 1, 2009 at 2:36 pm
Hi!
I really need some help here.
I'm the owner of a SQL Server Database application that lost three days data! I can't understand how or why.
So here is the set-up.
- SQL Server 2005 32bit standard edition database on Windows 2000 server. (Database B)
- Database is in simple recovery mode
- The database is connected as a subscriber to another database(SQL Server 2005 64bit enterprise edition on Win2k3 enterprise) using SQL Server continuous Merge Replication. (Database A)
DatabaseB was rebooted on night X as part of scheduled reboot. When the database came back up it was used as normal for a couple of days and data was created into it perfectly fine.
But then yesterday Day X + 4 it lost a lot of data.
Database B is on a server with another instance of SQL Server and they both started to run out of memory(conflicting with each other).
Here is the sequence of events from the event log when I think this happened.
AppDomain 2 (DatabaseB.dbo[runtime].1) is marked for unload due to memory pressure.
AppDomain 2 (DatabaseB.dbo[runtime].1) unloaded.
BACKUP LOG WITH TRUNCATE_ONLY or WITH NO_LOG is deprecated.
The simple recovery model should be used to automatically truncate the transaction log. (on DatabaseB)
AppDomain 3 (DatabaseB.dbo[runtime].2) created.
I know the data is missing because of my audit logs and that a user had taken a screen shot of some of the data before it was deleted.
So here is my dilema...how could this have happened?
How can several days data go missing from DatabaseB?? (it subsequently is missing from the publication db also!)
Did the truncate with the Appdomain down cause the data to be flushed from the log?
Any and all theories considered. If anyone needs more data I can add it.
Help!
April 1, 2009 at 3:39 pm
First of all, to PREVENT this from happening gain, please PUT the DBs into FULL recovery mode and Schedule Regular Transcatinal LOG backup.
Secondly, as your DB is in simple recovery mode, any modifications (including deleting) that are done and commited are solid and firm and permanant, no backway, as your log file only records the active transaction logs. Commited transaction logs are flushed.
I would try to get the best possible backup and then use the audit log and default trace info to retrieve more information back. (save the trace file into a different folder, in case the old valuable ones will be flushed )
April 1, 2009 at 4:08 pm
ed (4/1/2009)
How can several days data go missing from DatabaseB?? (it subsequently is missing from the publication db also!)
Someone deleted it.
Did the truncate with the Appdomain down cause the data to be flushed from the log?
No, because data isn't in the log. The log is there for database consistency, for transaction rollbacks and (in full and bulk-logged recovery) for recoverability.
Truncating the log will never cause data loss of any form. The only log records that SQL will discard are one s that are inactive (committed transactions and the data pages have been flushed to disk). In addition, in simple recovery, a log truncation occurs automatically every minute or so (when a checkpoint occurs)
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
April 1, 2009 at 6:23 pm
I agree with Gail. I have seen several situations very similar to this on Replicated SQL servers where a whole bunch of data "just disappeared" and was blamed on replication or all kinds of other things.
Every single time it finally turned out to be either a mistake by someone with DBO rights or else a bug in the application. In short, the data was gone because someone deleted it.
What I learned from a couple of weeks investigating the first one was this simple fact: if it disappears from both sides of a replicated data stream, then it was because some Admin or App deleted it from the source and Replication then faithfully replicated the deletion everywhere else. Because hardware failures, configuration failures, log file failures, backup failures, network failures, etc., etc. will not be replicated.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 2, 2009 at 2:02 am
sunny Brook (4/1/2009)
First of all, to PREVENT this from happening gain, please PUT the DBs into FULL recovery mode and Schedule Regular Transcatinal LOG backup.Secondly, as your DB is in simple recovery mode, any modifications (including deleting) that are done and commited are solid and firm and permanant, no backway, as your log file only records the active transaction logs. Commited transaction logs are flushed.
I would try to get the best possible backup and then use the audit log and default trace info to retrieve more information back. (save the trace file into a different folder, in case the old valuable ones will be flushed )
Thanks for the advice Sunny Brook.
I had a back-up from the night before which had 90% of the data, we don't have that much of a problem retrieving the data, hence our 1 day data loss SLA.
GilaMonster : To be honest I suspected that it was the application but I wanted to make sure the log was not causing some weird problem with the memory issue that cropped up (the server is configured to limit memory now on both instances)
RBarryYoung : Thanks for the advice, replication still causes me a bit of pain in it's nuances. I will look more closely at the audit logs and see what could have caused this.
Thanks guys!
April 2, 2009 at 2:26 am
sunny Brook (4/1/2009)
First of all, to PREVENT this from happening gain, please PUT the DBs into FULL recovery mode and Schedule Regular Transcatinal LOG backup.
Full recovery is not going to prevent someone from deleting data
Secondly, as your DB is in simple recovery mode, any modifications (including deleting) that are done and commited are solid and firm and permanant, no backway, as your log file only records the active transaction logs. Commited transaction logs are flushed.
All committed changes are solid, firm and permanent in full recovery as well. The only way to 'undo' a committed transaction is to restore from backup
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